Updating Custom Menu Code References when New Workbook is saved

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Bob Phillips]

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)