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.

 

[somnath_it2006]

Try this:

Option Explicit

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
                .TooltipText = TipText(iCtr)
            End With
        Next iCtr
    End With
    
    [COLOR="Red"]Application.CommandBars(ToolBarName).Controls("MyMacro1").FaceId = [COLOR="Lime"]125[/COLOR]
    Application.CommandBars(ToolBarName).Controls("MyMacro2").FaceId = [COLOR="lime"]124[/COLOR]
    Application.CommandBars(ToolBarName).Controls("MyMacro3").FaceId = [COLOR="lime"]652[/COLOR][/COLOR]
    
End Sub

Add that red lines to your code.
Change the face id's whatever you want to assign (at the place of green digits).

 

[abhay_547]

Thanks a lot it's working now.