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

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

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

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