Gruppe
Menue
Problem
Bei Aktivierung von Tabelle 2 soll eine benutzerdefinierte Menüleiste angezeigt, bei Deaktivierung ausgeblendet werden.
ClassModule: DieseArbeitsmappe
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo 0
Application.CommandBars("MeineMenueLeiste").Delete
On Error GoTo 0
End Sub
Private Sub Workbook_Open()
Dim objBar As CommandBar
Dim objPopUp As CommandBarPopup
Dim objBtn As CommandBarButton
Dim strBar As String
strBar = "MeineMenueLeiste"
On Error Resume Next
Application.CommandBars(strBar).Delete
On Error GoTo 0
Set objBar = Application.CommandBars.Add( _
Name:=strBar, _
Position:=msoBarTop, _
MenuBar:=True, _
Temporary:=True)
Set objPopUp = objBar.Controls.Add(Type:=msoControlPopup)
objPopUp.Caption = "Meine Dateien"
Set objBtn = objPopUp.Controls.Add
With objBtn
.Caption = "Öffnen"
.OnAction = "FilesOpen"
.Style = msoButtonCaption
End With
'objBar.Visible = True
End Sub
StandardModule: Modul1
Sub FilesOpen()
MsgBox "Kein Code hinterlegt!"
End Sub
ClassModule: Tabelle2
Private Sub Worksheet_Activate()
With Application
.CommandBars("Worksheet Menu Bar").Enabled = False
With .CommandBars("MeineMenueLeiste")
.Enabled = True
.Visible = True
End With
End With
End Sub
Private Sub Worksheet_Deactivate()
With Application
.CommandBars("Worksheet Menu Bar").Enabled = True
With .CommandBars("MeineMenueLeiste")
.Enabled = False
.Visible = False
End With
End With
End Sub