Will man in einem Excel-VBA-Projekt per Programm auf bestimmte Tabellenblätter zugreifen, so eignet sich dazu hervorragend die Eigenschaft CodeName eines Blattes
(siehe Bild 1).
Diese Eigenschaft
- wird von Excel vergeben,
- folgt dem Muster "Tabelle1", "Tabelle2" usw. und
- kann von einem Anwender nicht geändert werden.
Benutzt der Entwickler den CodeName für den Zugriff auf ein Tabellenblatt, so kann der Anwender also den Namen des Blattes nach Belieben ändern, ohne die VBA-Anwendung dadurch in Schwierigkeiten zu bringen. Die Excel-Mappe muss nicht gesperrt werden.
Für den Entwicker wäre es allerdings wünschenswert, dass der CodeName erkennen lässt, welche Funktion das Tabellenblatt in der Anwendung hat (siehe Bild 2).
Im Folgenden zeige ich,
- wie die Eigenschaft CodeName per VBA geändert werden kann und
- das Tabellenblatt mit einem bestimmten CodeName per VBA angesprochen wird.
Voraussetzung für die Lösung ist, dass im Trust Center von Excel für die Änderung des CodeName vorübergehend das Vertrauen zum VBA-Projekt hergestellt wird (siehe Bild 3).
Die folgende Prozedur ändert die CodeName-Eigenschaft der Tabellenblätter.
Hinweis: CodeNames müssen mit einem Buchstaben beginnen.
Sub Change_CodeName()
With ThisWorkbook.VBProject
.VBComponents("Tabelle1").Properties("_CodeName").Value = "Dashboard"
.VBComponents("Tabelle2").Properties("_CodeName").Value = "Datenbank"
.VBComponents("Tabelle3").Properties("_CodeName").Value = "Auswertung"
End With
End Sub
Um ein spezielles Tabellenblatt in der aktuellen Mappe im Programmcode anzusprechen, kann einfach der CodeName verwendet werden.
Das Aktivieren des Dashboard-Tabellenblatts realisiert dann zum Beispiel die folgenden Zeile.
Dashboard.Activate
(Vielen Dank🌼 an Mike für den Tipp!)
Übrigens:
Der CodeName eines Tabellenblattes kann auch manuelle geändert werden.
- Wechsle dazu in den VBA-Editor,
- wähle Dir im Projektexplorer das Tabellenblatt, dessen CodeName Du ändern möchtest, und
- ändere im Eigenschaftenfenster (F4) die Eigenschaft Name.
Sabine Konschak (Autor) (Donnerstag, 04 November 2021 12:09)
Danke, Mike, für den Tipp. Ich habe den Beitrag entsprechend angepasst.
Mike (Mittwoch, 03 November 2021 13:25)
Hallo,
die Funktion für das Ansprechen des Tabellenblattes über den CodeName ist überflüssig, da man jedes Tabellenblatt in VBA direkt mit dem CodeName ansprechen kann, in obigem Beispiel also z.B. einfach mit "Dashboard.Activate"
mfg
Mike
Jürgen Kolb (Sonntag, 31 Oktober 2021 17:59)
...vermute, dass die Datei (Download) schon ausgeführt war und so den Fehler 9 nach direktem Ausführen nach dem Download produziert.
Setze ich die Eigenschaft auf Tabelle1...Tabelle3 handisch zurück und führe dann das Macro aus, keine Fehlermeldung. Erst beim zweiten Versuch (usw.) folgt die Fehlermeldung.
mfg
JK
Sabine Konschak (Montag, 20 September 2021 13:40)
Hallo Jürgen,
den Code dieses Blogs habe ich gerade noch einmal geprüft. Alles i.O! Den Laufzeitfehler 9 konnte ich allerdings provozieren, wenn ich in der Prozedur "Change_CodeName" einen Tabellennamen verwendet habe, den es in der Mappe nicht gibt, also zum Beispiel "Tabelle 1" statt "Tabelle1".
Jürgen Michels (Montag, 20 September 2021 11:13)
Das funktioniert bei mir leider nicht, ich bekomme den Laufzeitfehler 9 :"Index außerhalb des gültigen Bereichs"