Makros sollen auf allen Sheets gehen

Bild

Betrifft: Makros sollen auf allen Sheets gehen
von: Moritz
Geschrieben am: 05.10.2015 16:57:32

Hi,
also ich habe es nun endlich geschafft das alle meine Makros auf meinem Worksheet so funktionieren wie ich mir das vorgestellt habe. ( Ich beziehe mich bei meinem Makros schon immer auf das ActiveSheet )
Jetzt wollte ich das nächste Worksheet hinzufügen was komplett gleich seien soll, nur der Name des Worksheets will ich verändern.
Jetzt musste ich bestürzt festellen das man einen Tabellen Name nur 1 mal im Workbook vergeben darf, dadurch müsste ich komplett alle Makros wieder umschreiben und erstmal testen welches Worksheet um dann den Tabellen namen festzulegen.
Gibt es da vielleicht eine einfachere/elegantere Variante um mein Problem zu lösen.
Ich hoffe ich konnte mein Problem klar und verständlich verdeutlichen, ich bin gerade ein wenig aufgebracht.
Ich häng meine Excel Datei noch ran damit ihr, falls gewollt, gerne anschauen dürft.
[Link]www.herber.de/bbs/user/100602.xlsm[/Link]
Mit freundlichen Grüßen
Moritz

Bild

Betrifft: vielleicht so, aber kaum elegant
von: Michael
Geschrieben am: 05.10.2015 17:30:39
Hallo Moritz,
Du greifst durchweg nicht auf den Namen des *Tabellenblatts* zu, derweil Du immer activesheet verwendest.
Der Name des Blattes ist insofern egal.
Was zu den Problemen führt, sind leider die Bereichsnamen, die Du verwendest, z.B. geplantInhouse
Wenn ich das eine, vorhandene Blatt kopiere, wird eine Kopie erzeugt, die dann geplantInhouse8 heißt (für andere Namen werden leider andere Nummern "automatisch" vergeben.
Vielleicht solltest Du ein bis auf die Namen komplettes Blatt als Kopiervorlage anlegen und ein Makro schreiben, das die Namen bei jedem neuen Blatt erzeugt, nach dem Motto:
BereichsName & activesheet.index
Dann brauchst Du nur *einmalig* Deine Makros nach den Bereichsnamen durchsuchen und ersetzen durch den Namen & activesheet.index.
Also, so daß etwa aus der Zeile ...

Set Tbl = ActiveSheet.Range("geplantInhouse").ListObject

wird:
Set Tbl = ActiveSheet.Range("geplantInhouse" & activesheet.index).ListObject
Das sind 11 Ersetzungen, das ist überschaubar.
Allerdings nicht besonders elegant - vielleicht weiß einer der Profis was Besseres.
Schöne Grüße,
Michael

Bild

Betrifft: AW: vielleicht so, aber kaum elegant
von: Moritz
Geschrieben am: 06.10.2015 08:58:42
Danke Michael für deine Antwort, du benutz ja im Grunde den gleichen gedanken wie Lushi, nur ein wenig anders.
Ich danke dir für deine Antwort

Bild

Betrifft: Deine Linkkennzeichnung ist hier nicht nötig, ...
von: Luc:-?
Geschrieben am: 05.10.2015 17:38:40
…Moritz;
das Herber-Forum verwendet nur HTML-Tags, keine Substitute ala […]! Also entweder gar nicht kennzeichnen (wie dir das auch der Server vorgibt → ForumsAutomatik) oder wie in HTML üblich.
Gruß, Luc :-?

Bild

Betrifft: AW: Deine Linkkennzeichnung ist hier nicht nötig, ...
von: Moritz
Geschrieben am: 06.10.2015 08:57:33
Servus Luc:-?
wird nicht mehr vorkommen
sry

Bild

Betrifft: AW: Makros sollen auf allen Sheets gehen
von: Luschi
Geschrieben am: 05.10.2015 18:36:42
Hallo Moritz,
hier mal mein Ansatz:


- ich habe die Tabelle 'CoC Ausstattung' einfach kopiert
- im Namensmanager alle Namen einheitlich umbenannt, die im Namen eine Zahl als Suffix
  hatten (willkürlich von Excel automatisch vergeben) mit dem neuen Suffix '_2'
- im Vba-Code ein neues Modul angelegt
- in dem für die Namen Variablen definiert werden  
- die Belegung der Variablen erfolgt in der Prozedur:
  Private Sub Workbook_SheetActivate(ByVal Sh As Object)
- aus der Programmzeile:
  Set lstObj = ActiveSheet.ListObjects("ProjekteInhouse")
  wird
  Set lstObj = ActiveSheet.ListObjects(ProjekteInhouse) usw.
- und schon läuft der Code auch im neuen Tabellenblatt
https://www.herber.de/bbs/user/100603.xlsm
Gruß von Luschi
aus klein-Paris

Bild

Betrifft: AW: Makros sollen auf allen Sheets gehen
von: Moritz
Geschrieben am: 06.10.2015 08:56:48
WoW!
Vielen vielen Dank!!!!!
Ich bin mir sicher das ich auf diese Idee nie gekommen wäre weil ich garnicht wusste das man das machen kann xD
Danke.

Bild

Betrifft: AW: Makros sollen auf allen Sheets gehen
von: Moritz
Geschrieben am: 06.10.2015 09:01:56
Eine Frage hab ich noch, wenn ich die Excel öffnen funktionieren die Makros alle nicht, ich kriege folgenden Fehler
Laufzeitfehler '9':
Index außerhalb des gültigen Bereichs
Den Fehler kann ich total leicht umgehen in dem ich auf ein anderes Sheet gehen und wieder zurück.( das könnte ich ja auch beim Start der Excel Datei mit einbauen) Aber was den Fehler hervorruft interessiert mich trotzdem.
Hast du eine Idee?

Bild

Betrifft: AW: Makros sollen auf allen Sheets gehen
von: Luschi
Geschrieben am: 06.10.2015 10:22:19
Hallo Moritz,
da beim Öffnen der Arbeitsmappe das Ereignis 'Workbook_SheetActivate' nicht aufgerufen wird,
egal in welcher Excelversion bis E-2016, sind die Variablen nicht gesetzt sondern leer.
Deshalb mußte ich ein bischen umorganisieren:


Private Sub Workbook_Open()
   'Dein bisheriger Code
   'neuer Aufruf in letzter Zeile
   Call VariablenSetzen(ActiveSheet)
End Sub

Das Modul 'mod_Variablen' habe ich umbenannt in 'mod_Tools' und das sieht jetzt so aus:

Option Explicit
Public ProjekteInhouse As String, ProjekteResident As String, _
       geplantInhouse As String, geplantResident As String, _
       SummeMA As String
Sub VariablenSetzen(ByVal ws As Worksheet)
    Select Case ws.Name
     Case "CoC Ausstattung"
         ProjekteInhouse = "ProjekteInhouse"
         ProjekteResident = "ProjekteResident"
         geplantInhouse = "geplantInhouse"
         geplantResident = "geplantResident"
         SummeMA = SummeMA
     Case "CoC Ausstattung (2)"
         ProjekteInhouse = "ProjekteInhouse_2"
         ProjekteResident = "ProjekteResident_2"
         geplantInhouse = "geplantInhouse_2"
         geplantResident = "geplantResident_2"
         SummeMA = "SummeMA_2"
     End Select
End Sub
Und die Aktions-Routine beim Wechsel der Arbeitsblätter so:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Type = xlWorksheet Then
Call VariablenSetzen(Sh)
End If
End Sub
Hier auch noch die Datei dazu:
https://www.herber.de/bbs/user/100613.xlsm

Gruß´von Luschi
aus klein-Paris

Bild

Betrifft: AW: Makros sollen auf allen Sheets gehen
von: Moritz
Geschrieben am: 06.10.2015 11:37:20
Servus Luschi,
danke dir für deine Hilfe!
Ich bin noch sehr unerfahren was VBA/Excel angeht, da ich aber einige Mappen erstellen werde, wirst du warscheinlich immer mal wieder ein Thread von mir sehen, auf dem ich mich sehr über deine Antwort freuen würde.
Mit freundlichen Grüßen
Mo

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Makros sollen auf allen Sheets gehen"