Gruppe
Menue
Problem
Wie kann ich feststellen, welche Schaltfläche aus einer benutzerdefinierten Menüleiste ein Makro aufgerufen hat?
ClassModule: DieseArbeitsmappe
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call CmdDelete
End Sub
Private Sub Workbook_Open()
Dim oBar As CommandBar
Dim oPopUp As CommandBarPopup
Dim oBtn As CommandBarButton
Dim iMonths As Integer, iDays As Integer
Call CmdDelete
Set oBar = Application.CommandBars.Add(Name:="J" & _
Year(Date), MenuBar:=True)
For iMonths = 1 To 12
Set oPopUp = oBar.Controls.Add(Type:=msoControlPopup)
oPopUp.Caption = Format(DateSerial(Year(Date), _
iMonths, 1), "mmmm")
For iDays = 1 To Day(DateSerial(Year(Date), iMonths + 1, 0))
Set oBtn = oPopUp.Controls.Add(Type:=msoControlButton)
With oBtn
.Caption = iDays
.OnAction = "Aufruf"
.Style = msoButtonCaption
End With
Next iDays
Next iMonths
Set oBtn = oBar.Controls.Add
With oBtn
.Caption = "Schließen"
.OnAction = "Beenden"
.Style = msoButtonCaption
End With
Application.CommandBars("J" & Year(Date)).Visible = True
Application.CommandBars("J" & Year(Date)).Enabled = True
End Sub
StandardModule: modMain
Sub Aufruf()
MsgBox Format(DateSerial(Year(Date), Application.Caller(2), _
Application.Caller(1)), "dd. mmmm yyyy")
End Sub
Sub CmdDelete()
On Error GoTo ERRORHANDLER
With Application
.CommandBars("J" & Year(Date)).Delete
.CommandBars("Worksheets Menu Bar").Visible = True
.CommandBars("Worksheets Menu Bar").Enabled = True
End With
ERRORHANDLER:
End Sub
Sub Beenden()
ThisWorkbook.Close
End Sub