PDA

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



abhay_547
04-09-2010, 02:44 PM
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
04-09-2010, 03:50 PM
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

abhay_547
04-09-2010, 11:43 PM
Thanks a lot it's working now. I can assign different face id's for each macro. :thumb

Bob Phillips
04-10-2010, 03:12 AM
Most people use a table driven approach. See http://spreadsheetpage.com/index.php/tip/creating_custom_menus/