MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Custom Menubar

Posted by Robb on February 06, 2002 12:07 PM

I want to dynamically add the "ZOOM drop-down box" to my custom menubar. Does anyone know the FaceID?

Posted by Russell Hauf on February 06, 2002 12:53 PM



Posted by Russell Hauf on February 06, 2002 1:00 PM

1733 is the ID, you don't need the face id for this...


Posted by Robb on February 06, 2002 1:21 PM

Re: Custom Toolbar (not menubar, sorry)

Sorry, my mistake, I am creating a TOOLBAR not menubar!!!

At the start of my program all Excel ToolBars are hidden, then I create my custom TOOLBAR using the code below. I need the Zoom Custom Combobox so the user can customize the view of the screen. This is what I have so far and it works fine except for the zoom cbo.

' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("Colour & Size Lists")

' Create the CommandBar called "Custom"
Set cbar1 = CommandBars.Add(Name:="Custom", Position:=msoBarTop)

' Initialize the row counter
Row = 400

' Add the control data stored on the MenuSheet
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
Caption = .Cells(Row, 1)
FaceId = .Cells(Row, 2)
OnAction = .Cells(Row, 3)
Style = .Cells(Row, 4)
TooltipText = .Cells(Row, 5)
BeginGroup = .Cells(Row, 6)
Enabled = .Cells(Row, 7)
End With

' Create the controls for "Custom" CommandBar
Set MenuObject = Application.CommandBars("Custom").Controls _
.Add(Type:=msoControlButton, Before:=1, _
MenuObject.Caption = Caption
MenuObject.FaceId = FaceId
MenuObject.OnAction = OnAction
MenuObject.Style = Style
MenuObject.TooltipText = TooltipText
MenuObject.BeginGroup = BeginGroup
MenuObject.Enabled = Enabled

' Move down a row to get the parameters for the next control
Row = Row + 1

' Show the "Custom" CommandBar
cbar1.Visible = True

Posted by Russell Hauf on February 06, 2002 3:48 PM

Re: Custom Toolbar (not menubar, sorry)

Your problem is that the Zoom combo box is not a command bar button, it's a drop down. So when you specify the type as msoControlButton, you are eliminating your chances of adding Zoom. Maybe you ought to add a "Type" column to your spreadsheet. However, you will then need to control your adding using, say, an If-Then statement. For example,

If Type = "Dropdown" Then
.Add(Type=msoControlDropdown, 1733)
' Do what you have above
End If

Hope this helps,