VBA-Programmierung in Microsoft Excel

Auflisten von OptionButtons in Kontextmenü

ClassModule: DieseArbeitsmappe

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Call CmdDelete
End Sub

ClassModule: Tabelle1

Private Sub Worksheet_BeforeRightClick( _
   ByVal Target As Range, Cancel As Boolean)
   Dim oBar As CommandBar
   Dim oButton As CommandBarButton
   Dim chb As Shape
   Cancel = True
   Call CmdDelete
   Set oBar = Application.CommandBars.Add("CheckBoxes", msoBarPopup)
   For Each chb In ActiveSheet.Shapes
      If Left(chb.Name, 12) = "OptionButton" Then
         Set oButton = oBar.Controls.Add
         With oButton
            .Caption = chb.Name
            .OnAction = "Positionieren"
            .Style = msoButtonCaption
         End With
      End If
   Next chb
   oBar.ShowPopup
End Sub

StandardModule: basMain

Sub Positionieren()
   Tabelle1.Shapes(Application.Caller(1)) _
      .TopLeftCell.Select
End Sub

Sub CmdDelete()
   On Error GoTo ERRORHANDLER
   Application.CommandBars("CheckBoxes").Delete
ERRORHANDLER:
End Sub

    

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