Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Die Excel/VBA-Beispiele

Thema

Aufrufende Menüleistenschaltfläche ermitteln

Gruppe

Button

Problem

Wie kann ich feststellen, welche Schaltfläche aus einer benutzerdefinierten Menüleiste ein Makro aufgerufen hat?

Lösung
Geben Sie den Ereigniscode in das Klassenmodul der Arbeitsmappe ein.

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

    

Beiträge aus dem Excel-Forum zu den Themen Menue und Button