ExcelMenu_onfly

Creates Excel menu on the fly, list of menu items, command actions, and icons need to be in format of ANStrList.
Icons to be passed are FaceIDs, Excel FaceIDs, get list of FaceIDs from http://Mydev.net/?DevID=J9GIMEFSG9
Tested in Excel 2016

Sub ExcelMenu_onfly(ANStrListCaptions, ANStrListFaceIDs, ANStrListCommands)
 Dim NewToolbar As CommandBar
 Dim NewButton As CommandBarButton
 ' Delete existing FaceIds toolbar if it exists
 CMName = "CustomExcelMenu445511"
 SepaCol = "{{$C$}}"
 On Error Resume Next
 Application.CommandBars(CMName).Delete
 On Error GoTo 0
 X1 = 0
 With Application.CommandBars.Add(Name:=CMName, _
  Position:=msoBarPopup, MenuBar:=False, temporary:=True)
  For Each MenID In Split(ANStrListCaptions, SepaCol)
   If MenID > "" Then
    X1 = X1 + 1
    With .Controls.Add(Type:=msoControlButton)
     If i / 10 = Int(i / 10) Then .BeginGroup = True
     .Caption = CutString3(ANStrListCaptions, X1, SepaCol) ' "FaceID = " & i
     .FaceId = CutString3(ANStrListFaceIDs, X1, SepaCol) ' i '346
     .OnAction = CutString3(ANStrListCommands, X1, SepaCol)
     '.OnAction = "'" & ThisWorkbook.Name & "'!" & "ValidateRow"
    End With
   End If
  Next
 End With
 Application.CommandBars(CMName).Width = 4500
 Application.CommandBars(CMName).ShowPopup
End Sub

ANStrListCaptions, ANStrListFaceIDs, ANStrListCommands

ExcelMenu_onfly &#34;Caption1{{$C$}}Caption2&#34;,&#34;44{{$C$}}55&#34;,&#34;Command1{{$C$}}Command2&#34;<br/>see screenshots

Views 385 Downloads 150

'CutString3', 'Excel', 'menu', 'right-click', 'faceids', 'commands', 'onClick', 'onAction', 'submenu'

ANmarAmdeen
324
Attachments
UI VBA-Excel
Revisions

v1.0