Customizing VB Macro on Menu Bar

[Keda Wang]

I'm trying to add macro buttons to the menu bar when the
correct excel file is loaded. In another words, when the
Excel file with the corresponding VB procedures (the macro
buttons will be calling) is loaded, the customized macro
buttons will be added to the menu bar. The buttons will
disappear or unload when the excel file quits.

I have been able to create such a macro button on the menu
bar, however, the buttons stay resident (permanently) on
my user account. It appears as a personal preference. I
want this to be a global preference. For example, when I
have another user open the file, the macro buttons do not
appear on the menu bar until they manually add the macro
button. I want any user whom open the excel file to have
the custom macro buttons created on the menu bar.

I know the macro buttons can be added to the menu bar
automatically when the excel file loads. I hope I am clear
about what I am trying to do and what the problem is.
Anyone have any suggestions? Thanks!

 

[Bob Phillips]

Keda,

The general approach is to add the menu in the Workbook_Open event, and
delete it again in the Workbook_BeforeClose event.

Here is an example

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("MyMenu").Delete
On Error GoTo 0

End Sub

Private Sub Workbook_Open()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("MyMenu").Delete
On Error GoTo 0

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

iHelpMenu = cbMainMenuBar.Controls("Help").Index

Set cbcCustomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
Before:=iHelpMenu, temporary:=True)
cbcCustomMenu.Caption = "MyMenu"

With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "item 1"
.OnAction = "macro1"
End With
With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "item 2"
.OnAction = "macro2"
End With
With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "item 3"
.OnAction = "macro3"
End With
End Sub




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Keda Wang]

Bob,

Thanks for the reply! Your example worked like a charm!
Cheers!

-Keda

 

[Bob Phillips]

That's great Keda, glad I Could help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Using your example, is there a way for another different workbook to add a 4th item to this custom menu?

 

[Bob Phillips]

Tommy,

Yes, this is the sort of thing

Dim cbMainMenuBar As CommandBar
Dim cbcCustomMenu As CommandBarControl

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

Set cbcCustomMenu = cbMainMenuBar.Controls("MyMenu")

With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "An extra item"
.OnAction = "macro99"
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Using your example, is there a way for another different workbook to add a

4th item to this custom menu?