How to use different Face ID's in a Toolbar.

abhay_547 · #1 (**permalink**) 04-09-10, 17:34

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. Please expedite.

HTML Code:

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.

Colin Legg · #2 (**permalink**) 04-09-10, 18:48

Hi,

Your code looks fine.

I ran it and -
It added a commandbar.
It added 3 commandbarbuttons to the commandbar.
Each commandbarbutton has a different faceid.

Quote:

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 don't quite understand the question/problem. You say the code works fine, so I'm assuming that it does the same for you as it did for me. Do you mean that if you try to change the faceid of one of the commandbarbuttons manually then the others change too?

abhay_547 · #3 (**permalink**) 04-10-10, 02:17

Hi Colin,

Your understanding is correct. When I try to change the faceid of one of the commandbar buttons manually then the other 2 macro button face id's also change. I want to assign different face id's for all 3 macros for example. I want to assign face id 1102 to my macro1 and faceid 98 to macro2 and faceid 208 to macro3. Please let me know what change should i make to my macro in order to assign different face id to each macro in my commandbar.

Thanks for your help in advance.