Ich sitze nun schon fast einen ganzen Tag an diesem Problem.
Ich moechte Ereignisprozeduren in zur Laufzeit erstellten Tabellenblaettern einfuegen.
Problem : Der Codename des neu eingefuegten Blattes ist erst nach verlassen des Makros oder mit geoeffnetem VB-Editor über z.B. Worksheets.codename auslesbar.
Loesung : kurzzeitiges oeffnen des VBE - Editors zur Laufzeit also wie schon einmal Thema hier:
With Application.VBE.MainWindow
.SetFocus
.Visible = True
End With
Sheets.Add.Name = "CONTROL"
codename_of_sheet = Sheets("CONTROL").CodeName
With ThisWorkbook.VBProject.VBComponents(codename_of_sheet).CodeModule
.AddFromString "' " & codename_of_sheet
End With
With Application.VBE.MainWindow
.SetFocus
.Visible = False
End With
end sub
Problem : ich moechte mich nicht mit einem flackernden Programm unternehmensweit und vor mir selber blamieren
2. gefundene Loesung von Heiko S.:
' Name festlegen
strNameOfSheet = "CONTROL"
' Neues Sheet anlegen
Sheets.Add
' Nun erstmal den Namen auslesen um auch den "VBA-Namen" zu bekommen.
strHilf = ActiveSheet.Name
' Und dann erst umbenennen
Sheets(strHilf).Name = strNameOfSheet
' Diesee Zeile kann auch weggelassen werden, dann bleibt der VBA-Name halt Tabelle...
ThisWorkbook.VBProject.VBComponents.Item(strHilf).Name = strNameOfSheet
' Ohne die Zeile über "mir" müßte die With Zeile so aussehen.
' With ThisWorkbook.VBProject.VBComponents(strhilf).CodeModule
With ThisWorkbook.VBProject.VBComponents(strNameOfSheet).CodeModule
.AddFromString "' " & strNameOfSheet
End With
end sub
Nun kommt mein Problem: An sich war ich von obiger Loesung zunaechst begeistert. Allerdings funktioniert sie bei mir nicht und zwar aus folgendem Grund: bei der Erzeugung meines Tabellenblattes bekommt das Blatt als Namen z.B.: Tabelle5 und als Codenamen Tabelle7 (unter Testbedingungen momentan ist die Nummer des Codenamens immer um 2 hoeher als der Tabellenname). Ich schaetze, dass dies daran liegt, dass ich vorher 2 Pivottabellenberichte einfuege und zwar auf folgende Art und Weise:
Sub pivot(xxx As String, yyy As String, zzz As String, Bname As String, tname As String, _
dname _
_
As String)
'erstellen des Pivottables
Worksheets(Bname).Activate
Worksheets(Bname).PivotTableWizard SourceType:=xlDatabase, _
SourceData:=rngrange, TableDestination:=Worksheets(Bname).Range("a1"), tablename:= _
tname
With ActiveSheet.PivotTables(tname)
.PivotFields(xxx).Orientation = xlPageField
.PivotFields(xxx).Orientation = xlRowField
.PivotFields(yyy).Orientation = xlColumnField
.PivotFields(zzz).Orientation = xlDataField
End With
End Sub
Das Problem ist dass ich eine unbestimmte Zahl an Pivottables einfuege und so spaeter nicht mehr so einfach
sagen kann welche Differenz nun zwischen Blattnamennummer und Codenamennummer ist. Natuerlich koennte ich nun
eine globale Integervariable die eingefuegten Pivottables zaehlen lassen und dann immer den Codenamen ausrechnen.
Aber ich fuege sehr viele Blaetter ueber Prozeduren ein und ich habe einfach keine Lust auf diese Spielchen.
Hat jemand eine elegante Loesung fuer dieses Problem ?
mit besten Grueßen,
R. Z.
P.S. : Codenamen aendern funktioniert auch nicht, da ich dazu ja auch den Codenamen brauche. Eine Loesung
waere vielleicht wenn ich folgende Formel verwenden koennte ohne den Codenamen uebergeben zu muessen und
stattdessen nur den Blattnamen:
' zielname ist hier ein string der den codenamen darstellt
With ActiveWorkbook.VBProject.VBComponents(zielname).CodeModule
ii = .CreateEventProc("SelectionChange", "worksheet") + 1
end sub