Excel-VBA: Makroaufruf durch Menüeintrag
Für viele Kleinigkeiten des täglichen Excel-Arbeitslebens bietet es sich an Makros zu erstellen. Eintönige Routineaufgaben lassen sich damit automatisieren und die Tabellenkalkulation um zahlreiche individuelle Features ergänzen.
Spätestens, wenn man die kleinen Helfer dann nicht nur selber nutzen, sondern auch etwa den Kollegen bereitstellen möchte, macht es teilweise Sinn, sie in einem Add-In zu bündeln und mit Menüeinträgen den komfortablen Aufruf zu ermöglichen. Wie das geht, fasse ich hier fix zusammen.
In meiner Arbeitsmappe finden sich sämtliche Module, Prozeduren und Funktionen, die wir in der Firma regelmäßig verwenden. Als Add-In wird diese nun stets geöffnet, wenn man Excel startet und bohrt als erstes durch folgenden Code im Bereich Workbook_Open das Menü auf:
1 2 3 4 5 6 7 8 9 10 11 12 13 | Option Explicit Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Controls("Daten").Reset Dim myCommandBar As CommandBar Dim myCommandBarPopup As CommandBarPopup Dim myCommandBarButton As CommandBarButton Set myCommandBar = Application.CommandBars("Worksheet Menu Bar") Set myCommandBarPopup = myCommandBar.Controls("Daten") Set myCommandBarButton = myCommandBarPopup.Controls.Add(Type:=msoControlButton, _ before:=myCommandBarPopup.Controls.Count, temporary:=True) |
In diesem Fall nutzen wir das vorhandene Standardmenü Daten, welches erst einmal zurückgesetzt wird und somit keine ungewollten doppelten Einträge enthalten kann. Mit der add-Methode ließe sich auch ein neues Menü (= CommandBarPopup) erstellen, um alle Tools zu gruppieren. Den eigentliche Menüeintrag (myCommandBarButton ) platzieren wir mit der Eigenschaft before am Ende des Menüs.
Nun definieren wir diesen wie folgt:
14 15 16 17 18 19 20 21 22 | With myCommandBarButton .BeginGroup = True .Caption = "Mein Makro" .FaceId = 343 .OnAction = "Supermakro" .Tag = "Supermakro" End With Set myCommandBarButton = Nothing |
Durch die Eigenschaft BeginGroup wird ein abgrenzender Strich über dem neuen Menüeintrag erzeugt und die auszuführende Prozedur heißt in diesem Fall Supermakro. Mit der FaceID wird ein Icon bestimmt – hier die Glühbirne.
Obwohl wir unseren Eintrag in Zeile 13 als temporären gekennzeichnet haben, räume ich immer gerne hinter mir auf. Folgendes schreiben wir daher in den BeforeClose-Teil der Arbeitsmappe:
23 24 25 26 27 | Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Daten").Reset On Error GoTo 0 End Sub |
Auch in das durch Klick der rechten Maustaste aufrufbare Kontextmenü lassen sich neue Einträge bringen. Hier ein Beispiel für das konventionelle Zellen-Menü:
28 29 30 31 32 33 34 35 | Set myCommandBarButton = Application.CommandBars("Cell").Controls.Add(before:=myCommandBarPopup.Controls.Count, temporary:=True) With myCommandBarButton .BeginGroup = True .Caption = "Mein Makro" .FaceId = 343 .OnAction = "Supermakro" .Tag = "Supermakro" End With |
Michel plappert:
11. Juni 2010 um 18:45
Cool, ich wußte garnicht dass das geht. Bin ja auch ein VBA-Jungie, nix geht mehr ohne und hab auch wirklich schon so ziemlich jeden Blödsinn oder auch eben nich Blödsinn in VBA programmiert, aber das hier wusste ich nicht, ich hab aber auch noch nie danach geschaut
jetzt weiß ich es. Danke:-)
AJ plappert:
14. Juni 2010 um 19:44
Ihh gittigittttt Excel? OpenOffice ist doch 1337 !
derhenry plappert:
15. Juni 2010 um 20:10
@Michel: Bitschön.
@AJ: Eigentlich bin ich ja auch Fan von Opensource. Aber gegen Excel hat leider keine Tabellenkalkulation eine Chance. Dummerweise auch Numbers nicht.