Customizing VB Macro on Menu Bar
 

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!

Customizing VB Macro on Menu Bar
 

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,
Befo=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" wrote in message
...
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!

Customizing VB Macro on Menu Bar
 

Bob,

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

-Keda
-----Original Message-----
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,
Befo=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" wrote
in message
...
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!


.

Customizing VB Macro on Menu Bar
 

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)

"Keda Wang" wrote in message
...
Bob,

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

-Keda

Customizing VB Macro on Menu Bar
 

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

Customizing VB Macro on Menu Bar
 

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)

"Tommy T" wrote in message
...
Using your example, is there a way for another different workbook to add a
4th item to this custom menu?