Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Custom Menu Code References when New Workbook is saved
Hi.
I'm using custom menu items to load and display forms in an Excel 2000 workbook, Application v1.0 If I save the workbook as Application v2.0 and click on a menu item to load a form, Excel goes and finds Application v1.0. How do I programmatically remove these links, so that Application v2.0 will use the code directly behind that workbook? TIA, Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Custom Menu Code References when New Workbook is saved
Are the menus manually built and assigned to macros?
If so, try creating them dynamically in Workbook_Open. Simple example Private Sub Workbook_Open() Dim oCb As CommandBar Dim oCtl As CommandBarPopup Dim oCtlBtn As CommandBarButton Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtl = .Controls("Tools").Controls.Add( _ Type:=msoControlPopup, _ temporary:=True) oCtl.Caption = "myButton" With oCtl Set oCtlBtn = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtlBtn.Caption = "myMacroButton" oCtlBtn.FaceId = 161 oCtlBtn.OnAction = "myMacro" End With With oCtl Set oCtlBtn = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtlBtn.Caption = "myMacroButton2" oCtlBtn.FaceId = 161 oCtlBtn.OnAction = "myMacro2" End With 'etc. End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCb As CommandBar Set oCb = Application.CommandBars("Worksheet Menu Bar") oCb.Controls("Tools").Controls("myButton").Delete End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JimRWR" wrote in message ... Hi. I'm using custom menu items to load and display forms in an Excel 2000 workbook, Application v1.0 If I save the workbook as Application v2.0 and click on a menu item to load a form, Excel goes and finds Application v1.0. How do I programmatically remove these links, so that Application v2.0 will use the code directly behind that workbook? TIA, Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Custom Menu Code References when New Workbook is saved
Thanks, Bob, that works. One other question - what property is available for
custom drop down menus? I tried manipulating the type property of the CommandBarControl object to msoControlButtonDropDown, but get the Invalid Procedure Call Argument (RTE 5) error. Jim "JimRWR" wrote: Hi. I'm using custom menu items to load and display forms in an Excel 2000 workbook, Application v1.0 If I save the workbook as Application v2.0 and click on a menu item to load a form, Excel goes and finds Application v1.0. How do I programmatically remove these links, so that Application v2.0 will use the code directly behind that workbook? TIA, Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Custom Menu Code References when New Workbook is saved
It is not a button so you can't use button.
Here is some example code, not the variable type has been changed for this Dim oCb As CommandBar Dim oCtl As CommandBarPopup Dim oCtlBtn As CommandBarControl Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtl = .Controls("Tools").Controls.Add( _ Type:=msoControlPopup, _ temporary:=True) oCtl.Caption = "myButton" With oCtl Set oCtlBtn = .Controls.Add( _ Type:=msoControlDropdown, _ temporary:=True) oCtlBtn.Caption = "myMacroButton" oCtlBtn.OnAction = "myMacro" oCtlBtn.AddItem "Bob" oCtlBtn.AddItem "Lynne" oCtlBtn.AddItem "Amy" oCtlBtn.AddItem "Hannah" oCtlBtn.AddItem "Brian" End With End With -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JimRWR" wrote in message ... Thanks, Bob, that works. One other question - what property is available for custom drop down menus? I tried manipulating the type property of the CommandBarControl object to msoControlButtonDropDown, but get the Invalid Procedure Call Argument (RTE 5) error. Jim "JimRWR" wrote: Hi. I'm using custom menu items to load and display forms in an Excel 2000 workbook, Application v1.0 If I save the workbook as Application v2.0 and click on a menu item to load a form, Excel goes and finds Application v1.0. How do I programmatically remove these links, so that Application v2.0 will use the code directly behind that workbook? TIA, Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code snippets for custom menu | Excel Programming | |||
Updating Ranges or References in Code when Adding or Deleting Rows | Excel Programming | |||
custom menu bar mispelled, where is the code for it? | Excel Programming | |||
Extracting Custom Menu Code | Excel Programming | |||
Deleting custom Commands from File Menu are nor saved next time w. | Excel Worksheet Functions |