Command Bar Creation

[Alberto Ast]

I have search on old posts on this topic and there are a lot of information
but maybe I need more basics on this... I have tried several of the options
but can not make appear any new command on my excel file command bar...

I have Excel 2002.... I copied below code from "Roman" from a post back in
2005 but not working for me.... maybe I am not loading it in the right
place... I try a module, I try in Private Sub Workbook_Open(), and try
whatever but not luck

Need help to create a new menu on my command bar that will work just with a
specific file...

Appreciate help

Sub makemenewbar()
Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With mynewbar
...Caption = "Name of new bar"
End With

Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
With button1
.Caption = "Button1"
.OnAction = "macro1"
End With

Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
With mysubmenu
.Caption = "Submenu1"
'.OnAction = "sheets_startuf"
End With

Set button2 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button2
.Caption = "Button2 name"
.OnAction = "macro2"
End With

Set button3 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button3
.Caption = "Button3 name"
.OnAction = "macro3"
End With
End Sub

 

[Peter T]

The code works fine for me, it puts a popup button on the main with some
other buttons.
Probably best to put the code in a normal module and test it. Leave the code
in the normal module and call from your open event.

Additionally, you might want to ensure the menu is deleted before attempting
to recreate it, eg

On Error Resume Next
CommandBars(1).Controls("Name of new bar").Delete
On Error GoTo 0

In your post there are two dots before ..Caption, I assume there's only one
in your actual code

Regards,
Peter T

 

[Alberto Ast]

Thanks Peter, got it.

The code works fine for me, it puts a popup button on the main with some
other buttons.
Probably best to put the code in a normal module and test it. Leave the code
in the normal module and call from your open event.

Additionally, you might want to ensure the menu is deleted before attempting
to recreate it, eg

On Error Resume Next
CommandBars(1).Controls("Name of new bar").Delete
On Error GoTo 0

In your post there are two dots before ..Caption, I assume there's only one
in your actual code

Regards,
Peter T






.