Gruppe
Menue
Problem
Die aktuell geöffneten Arbeitsmappen sollen in einer CommandBar-ComboBox in der Arbeitsblattmenüleiste zum Zweck der Auswahl gelistet werden.
ClassModule: DieseArbeitsmappe
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call CmdDelete
End Sub
Private Sub Workbook_Open()
Call NeuEinlesen
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Call NeuEinlesen
End Sub
StandardModule: basMain
Sub NeuEinlesen()
Dim oCbo As CommandBarComboBox
Dim wkb As Workbook
Call CmdDelete
Set oCbo = Application.CommandBars("Worksheet Menu Bar") _
.Controls.Add(msoControlComboBox)
With oCbo
.Caption = "Mappen"
For Each wkb In Workbooks
.AddItem wkb.Name
.OnAction = "BlattAuswahl"
Next wkb
.Text = ActiveWindow.Caption
End With
End Sub
Sub BlattAuswahl()
Application.EnableEvents = False
Windows(Application.CommandBars(1) _
.Controls("Mappen").Text).Activate
Application.EnableEvents = True
End Sub
Sub CmdDelete()
On Error GoTo ERRORHANDLER
Application.CommandBars("Worksheet Menu Bar") _
.Controls("Mappen").Delete
ERRORHANDLER:
End Sub