Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MACRO MENU | Excel Discussion (Misc queries) | |||
Customizing the menu in excel 2007 | Excel Discussion (Misc queries) | |||
Customizing Excel Worksheet Menu Bar without VBA coding | Excel Discussion (Misc queries) | |||
Customizing Worksheet Menu Bar for a workbook without VBA coding | Excel Discussion (Misc queries) | |||
Customizing Worksheet Menu Bar question. Help! | Excel Discussion (Misc queries) |