Commandbar

Last Update 20.07.2004

Zusätzlicher Menübefehl für eine Arbeitsmappe erstellen

Menübutton mit hinterlegtem Hyperlink erstellen

Textbox zur Suche in einer Menüleiste einblenden

IconID und Bezeichnung aller Icons der Symbolleisten in die Tabelle schreiben

Eigenen Befehl in das Kontextmenü des Tabellenregisters hinterlegen

Zusätzlicher Menüpunkt für eine Arbeitsmappe erstellen

Private Sub Workbook_Open() Dim cbMenu As CommandBar Dim cbSpecialMenu As CommandBarPopup Dim cbCommand As CommandBarControl 'Zuweisen der Objectvariablen Set cbMenu = Application.CommandBars("Worksheet Menu Bar") Set cbSpecialMenu = cbMenu.Controls.Add(Type:=msoControlPopup) 'Titelbeschriftung der Menübar cbSpecialMenu.Caption = "Mein Spezialmenu" 'Einen Button hinzufügen und diesen gleich beschriften Set cbCommand = cbSpecialMenu.Controls.Add(Type:=msoControlButton) cbCommand.Caption = "Mein Befehl" cbCommand.OnAction = "Ihr Makro das ausgeführt werden soll" End Sub
Mit diesen Anweisungen fügen Sie einen zusätzlichen Menüpunkt namens "Mein Spezialmenü" in die Standard-Menüleiste von Excel ein. Außerdem erweitern Sie das Menü um einen Befehl namens "Mein Befehl". Wenn dieser Befehl eine Aktion ausführen soll, müssen Sie nicht nur die "Caption"- sondern auch die "OnAction"-Eigenschaft setzen und ihr den Namen eines Makros zuweisen.

Erstellen Sie eine Prozedur, die das "Spezialmenü" beim Schließen der Datei wieder entfernt.
Im rechten Dropdown-Feld im VB-Editor aktivieren Sie dieses Mal aber das "BeforeClose"-Ereignis. Als Prozedurcode geben Sie folgendes ein:

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim cbSpecialMenu As CommandBarControl 'Fehlerbehandlung ausschalten 'sonst entsteht ein Fehler wenn die Menübar nicht mehr sichtbar ist On Error Resume Next 'Objektvariablen füllen Set cbSpecialMenu = Application.CommandBars("Worksheet Menu Bar").Controls("Spezialmenü") 'Menüleiste löschen cbSpecialMenu.Delete End Sub

Den Menübefehl müssen Sie nicht gesondert entfernen, da er zusammen mit dem übergeordneten Menü gelöscht wird.

Um den Menüzugriff ausschließlich im Rahmen der passenden Arbeitsmappe zu erlauben, fügen Sie auch Prozeduren für das "Deactivate"- sowie das "Activate"-Ereignis des "Workbook"-Objekts hinzu. Damit stellen sie nun endgültig sicher, dass die Menüleiste nur zur Verfügung steht, wenn ihre Arbeitsmappe auch tatsächlich aktiv ist.

Private Sub Workbook_Deactivate() On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Spezialmenü").Visible = False End Sub Private Sub Workbook_Activate() On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Spezialmenü").Visible = True End Sub

Nun können Sie die aktuelle Arbeitsmappe speichern und schließen. Beim nächsten Öffnen wird am rechten Ende der Excel-Menüleiste das neue Menü "Spezialmenü" erscheinen. Es verschwindet, sobald Sie die Mappe schließen oder eine andere Excel-Datei aktivieren. Wenn Sie zur ursprünglichen Mappe zurückkehren, steht Ihnen das Menü wieder zur Verfügung

Zurück

Einfügen eines Buttons mit Hyperlink in einer Symbolleiste

Erstellen Sie eine temporäre Symbolleiste mit einem Button hinter dem ein Hyperlink hinterlegt ist.

Sub Set_Hyperlink_Commandbar()
Dim myBar As CommandBar
Dim mybutton As CommandBarButton
Set myBar = CommandBars.Add(Name:="Hyper-Test", Position:=msoBarTop, Temporary:=True)
Set mybutton = myBar.Controls.Add(Type:=msoControlButton)
With mybutton
     .FaceId = 277
     .HyperlinkType = msoCommandBarButtonHyperlinkOpen
      'Nur wenn der Hyperlink im Tooltip steht funkioniert es :-))
      .TooltipText = "http://www.microsoft.com"
End With
Application.CommandBars("Hyper-Test").Visible = True
End Sub

Löschen Sie die Symbolleiste wieder.

Sub Del_Commandbar()
Application.CommandBars("Hyper-test").Delete
End Sub

Sie können diese beiden Codeteile auch in ein Workbook_Open und ein Workbook_Close Ereignis zuweisen. Dann wird die Symbolleiste beim öffnen der Datei erstellt und beim schliessen der Datei wieder entfernt.

Zurück

Textbox zur Suche in einer Menüleiste einblenden

Alle Codeteile gehören in die jeweiligen Module. Nach dem öffnen der so präparierten Arbeitsmappe steht eine neue zusätzliche Suchfunktion in der Symbolleiste zur Verfügung.

'Private Sub Workbook_Open()
CreateSearchBar
'End Sub

'allgemeines Modul
Sub CreateSearchBar()
'Menüleiste mit Textfeld und Button zum Suchen
Dim myCMB As CommandBar
Dim myCombo As CommandBarControl
Dim myBtn As CommandBarButton
'Bereits eine eventuell noch existierende Symbolleiste löschen
DeleteSearchBar
'Erstellen der Symbolleiste
With Application.Commandbars.Add(Name:="MySearch")
    'sichtbar
   .Visible = True
   'oberhalb der Tabelle
   .Position = msoBarTop
   Set myCombo = .Controls.Add(Type:=msoControlEdit, temporary:=True)
   Set myBtn = .Controls.Add(Type:=msoControlButton, temporary:=True)
End With
'Action der Editbox definieren wenn sie
'verlassen wird !!
With myCombo
   .OnAction = "Search"
   .Width = 150
End With
'bietet die gleiche Funktion nochmal
With myBtn
   .Caption = "Suchen"
   .OnAction = "Search"
   .Style = msoButtonIconAndCaption
   .FaceId = 141
End With
End Sub

Sub Search()
'Das Makro das ausgelöst wird zur Suche
Dim strSrch As String
strSrch = Application.Commandbars("MySearch").Controls(1).Text
If strSrch = "" Then Exit Sub
    Cells.Find(What:=strSrch, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
End Sub

Sub DeleteSearchBar()
'Sollte in das Workbook_BeforeClose Ereignis um
'die erstellte Symbolleiste zu netfernen
On Error Resume Next
Application.Commandbars("MySearch").Delete
End Sub

Zurück

ICON ID und ICON Bezeichnungen in die Tabelle schreiben

Wird manchmal benötigt um die entsprechenden ID's herauszufinden

Sub print_control_name()
'(C) Ramses
'Schreibt die ID und die Bezeichnung der einzelnen Symbole
'und Buttons in die aktive Tabelle !!
'Achtung: Existierende Daten in dieser Tabelle werden überschrieben
Dim cc As Integer, n As Long, i As Long
cc = 1
For n = 1 To Application.Commandbars.count
    Cells(1, cc) = Application.Commandbars(n).Name
    For i = 1 To Application.Commandbars(n).Controls.count
        Cells(i + 1, cc) = Application.Commandbars(n).Controls(i).ID
        Cells(i + 1, cc + 1) = Application.Commandbars(n).Controls(i).Caption
    Next i
    cc = cc + 2
Next n
End Sub

Zurück

Eigenen Befehl in das Kontextmenü des Tabellenregisters hinterlegen

Häufig gebraucht aber gut versteckt im Kontextmenü, geht es damit etwas einfacher.
Das Code "Create_New_Context_Command" kann auch in das Workbook_Open einer Mappe geschrieben werden

'Globale Konstante für den Namen des neuen Menüpunktes
'damit kann auch das "Delete_New_Context_Command" Makro
'auf den gleichen Namen zugreifen, und muss nur an einem Ort geändert werden
'Die Konstante gehört in ein MODUL
'(C) Ramses
Const myContext As String = "Neue Mappe aus Tabelle erstellen"

Sub Create_New_Context_Command()
Dim myNewContext As Object
Dim myContext As String
'Löschen eines bereits existierenden Menüs
'mit gleichem Namen
On Error Resume Next
'Commandbars("Ply") ist das Kontextmenü der Tabellenregister
Application.CommandBars("Ply").Controls(myContext).Delete
On Error GoTo 0
Set myNewContext = Application.CommandBars("Ply").Controls.Add
With myNewContext
.Caption = myContext
.OnAction = "Create_New_Workbook"
End With
End Sub

Sub Delete_New_Context_Command()
'Falls keine mehr existiert oder noch nicht ausgeführt
On Error Resume Next
Application.CommandBars("Ply").Controls(myContext).Delete
On Error GoTo 0
End Sub

'Das benötigte Makro für die "OnAction" - Methode des neuen
'Menüpunktes
Sub Create_New_Workbook()
'Erstellt aus der gerade aktiven Tabelle eine Arbeitsmappe
'in der nur die gerade aktive Tabelle enthalten ist
Worsheets.Copy
End Sub