AW: Makrozuweisung von CommandBarButton abfragen
18.09.2013 10:56:28
CommandBarButton
Hallo Martin,
mit folgender Ergänzung -wegen nicht verfügbarer Excelversion ungetestet- solltest du Excel auch die Informationen zu den PopUp-Menüs entlocken können.
Gruß
Franz
Sub makrotest()
Dim cmd As CommandBar, cntr As CommandBarControl, cntrPopup As CommandBarPopup
Dim objItem As Object
Dim Z As Long
On Error Resume Next 'evtl nicht erforderlich
Application.Calculation = xlCalculationManual
Z = 1
Cells.Clear
For Each cmd In Application.CommandBars
If cmd.Visible = True Then
For Each cntr In cmd.Controls
If cntr.BuiltIn Then
Cells(Z, 1) = cmd.Name & ": " & cntr.Caption: Cells(Z, 2) = "Original"
Else
If cntr.Type = msoControlPopup Then
Cells(Z, 1) = cmd.Name & ": " & cntr.Caption
Cells(Z, 2) = "PopUp-Control"
For Each objItem In cntrPopup.Controls
Z = Z + 1
Cells(Z, 1) = cmd.Name & ": " & cntr.Caption
Cells(Z, 2) = objItem.Caption
Cells(Z, 3) = objItem.OnAction
Next objItem
Else
Cells(Z, 1) = cmd.Name & ": " & cntr.Caption
Cells(Z, 2) = cntr.OnAction
End If
End If
Z = Z + 1
Next cntr
End If
Next cmd
Application.Calculation = xlAutomatic
End Sub