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/getarti...b_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

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.

--

HTH

Bob

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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/getarti...b_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

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" <(E-Mail Removed)> wrote:

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

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

--

HTH

Bob

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote:
>
>>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.

Oh well! Thanks Bob

"Bob Phillips" <(E-Mail Removed)> wrote:

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