Solved: How to assign different face id to macros in a toolbar

[abhay_547]

Hi All,

I have a code to create a toolbar in Excel. I use the below code to create an addin for my macros. Now I have 3 macros in my toolbar and but I can assign Face ID to only my first macro and other 2 macros get immediate next Face ID's assigned automatically. I want to assign different face ID for each macro. For e.g. If I mention in my code Face ID 1102 then it is assigned to my first macro button in my toolbar and by default my code assigns 1103 to Macro 2 button and 1104 to Macro 3 button. I want to assign different face ID's to all three of my macros in my toolbar. I should be able to mention different face id for each macro in my code. Following is my code. It works fine. I tried lot of this to assign different face id's in my code but nothing worked. Can some one suggest a change to my code which will help me to assign different Face ID's for my each macro in toolbar. I have highlighted the Face ID line in the below code. Please expedite.

Option Explicit

Public Const ToolBarName As String = "My Toolbar Bar"
'===========================================
Sub Auto_Open()
    Call CreateMenubar
End Sub

'===========================================
Sub Auto_Close()
    Call RemoveMenubar
End Sub

'===========================================
Sub RemoveMenubar()
    On Error Resume Next
    application.CommandBars(ToolBarName).Delete
    On Error GoTo 0
End Sub

'===========================================
Sub CreateMenubar()

    Dim iCtr As Long

    Dim MacNames As Variant
    Dim CapNamess As Variant
    Dim TipText As Variant

    Call RemoveMenubar

    MacNames = Array("Macro1", _
                     "Macro2", _
                     "Macro3")                                      
                    

    CapNamess = Array("MyMacro1", _
                       "MyMacro2", _
                       "MyMacro3")        
                       
                    
                      

    TipText = Array("Click to run Macro1", _
                    "Click to run Macro2", _
                    "Click to run Macro3", _
                    "Retrieve Data")
                    
                                
                   

    With application.CommandBars.Add
        .Name = ToolBarName
        .Left = 200
        .Top = 200
        .Protection = msoBarNoProtection
        .Visible = True
        .Position = msoBarFloating

        For iCtr = LBound(MacNames) To UBound(MacNames)
            With .Controls.Add(Type:=msoControlButton)
                .OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
                .Caption = CapNamess(iCtr)
                .Style = msoButtonIconAndCaption
                .FaceId = 1102 + iCtr
                .TooltipText = TipText(iCtr)
            End With
        Next iCtr
    End With
End Sub

Thanks for your help in advance.

[Bob Phillips]

[vba]

Sub CreateMenubar()
Dim iCtr As Long
Dim MacNames As Variant
Dim CapNamess As Variant
Dim TipText As Variant
Dim FaceIds As Variant

Call RemoveMenubar

MacNames = Array("Macro1", _
"Macro2", _
"Macro3")

CapNamess = Array("MyMacro1", _
"MyMacro2", _
"MyMacro3")

TipText = Array("Click to run Macro1", _
"Click to run Macro2", _
"Click to run Macro3", _
"Retrieve Data")

FaceIds = Array(1102, 284, 327)

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For iCtr = LBound(MacNames) To UBound(MacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = FaceIds(iCtr)
.TooltipText = TipText(iCtr)
End With
Next iCtr
End With
End Sub
[/vba]

[abhay_547]

Thanks a lot it's working now. I can assign different face id's for each macro.

[Bob Phillips]

Most people use a table driven approach. See http://spreadsheetpage.com/index.php..._custom_menus/