CommandBar and Controls (how to create separate "Sets" of Controls)

[EagleOne]

Excel 2003 & 2007 up to date


The code below was obtained from vbaexpress as noted. I modified it for my specifics.

For hours, I attempted to create a distinct Command Bar Menu "Set" of controls to no avail.

In short, my Add-Ins Tab in 2007 has two sections in the Menubar: ""Menu Commands" (which contains
5 dropdrown controls) and "Custom Toolbars" (which contains 3 .pdf controls). I would like my
menu item "&VBA Setup" to be in a separate section like ""Menu Commands" and "Custom Toolbars"
Thus 3 SECTIONS i.e. "Custom Toolbars;" "Custom Toolbars;" and "VBATools"

What code lines am I missing?

Any thoughts greatly appreciated.

***********************************************************************************************
CODE NEXT
***********************************************************************************************

Private Sub Workbook_Open()
' http://www.vbaexpress.com/kb/getarticle.php?kb_id=427#instr
' 3/22/2010

Dim cmbBar As CommandBar
Dim cmbControl As CommandBarControl

Set cmbBar = Application.CommandBars("Worksheet Menu Bar")

If cmbBar.Controls(cmbBar.Controls.count).Caption <> "" Then
Set cmbControl = cmbBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
End If
Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, Temporary:=True) 'adds a menu item
With cmbControl
.Caption = "&VBA Setup" 'names the menu item
With .Controls.Add(Type:=msoControlButton) 'adds a dropdown button to the menu item
.Caption = "Add-Ins Install" 'adds a description to the menu item
.OnAction = "ToolsInitDLL.AddinsInstall" 'runs the specified macro
.FaceId = 220 'assigns an icon to the dropdown
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Add-Ins Un-Install"
.OnAction = "ToolsInitDLL.AddInsUninstall"
.FaceId = 220
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Apply Macro Shortcuts"
.OnAction = "ToolsInitDLL.ApplyShortCuts"
.FaceId = 220
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "VB Library References"
.OnAction = "ToolsInitDLL.ListObjLibReferences"
.FaceId = 220
End With
End With
End Sub

 

[Bob Phillips]

You have no control over those groups, Excel 2007 maintains the two groups
and puts commandbars into them depending upon whether it is menus or
toolbars (in Excel 2003 speak). If you wan to control it, you have to create
your own ribbon elements.

 

[EagleOne]

Hello Bob!

Thanks. That is why it was not working!

What code would I need to force-place menu item (dropdown) "&VBA Setup"
at the Top of a "visible-column" in the group "Menu Commands." Currently the display shows the
"column" in groups of three (I realize that depends on HxW settings). My code does insert a "blank"
before "&VBA Setup" to provide some visual separation.

Appreciate your help! Bob.

 

[Bob Phillips]

As I said, you have no control, Excel decides that, presumably on the order
that the files are loaded.

 

[EagleOne]

Oh well! Thanks Bob