User defined menu stops working when workbook saved with a newname???

[alan82]

Hi all,

I have created my own menu for an Excel workbook using VBA set to run
when the Workbook is opened.

All the menu's work ok and call the correct macros until I save the
Workbook using a new name (workbook2.xls instead of workbook1.xls).
After I've done this, when selecting a menu item Excel displays and
error message saying that it cannot locate the original workbook
(workbook1.xls) and gives me no options to update a link.

All the macros are saved in the new workbook (woorkbook2.xls)

Does anybody know why this is happening??

Thanks in advance for your time,

A

 

[Jim Cone]

What is the code you use to assign macros to the menu items?
What version of Excel are you using?
--
Jim Cone
Portland, Oregon USA



"alan82" <[email protected]>
wrote in message
Hi all,
I have created my own menu for an Excel workbook using VBA set to run
when the Workbook is opened.
All the menu's work ok and call the correct macros until I save the
Workbook using a new name (workbook2.xls instead of workbook1.xls).
After I've done this, when selecting a menu item Excel displays and
error message saying that it cannot locate the original workbook
(workbook1.xls) and gives me no options to update a link.
All the macros are saved in the new workbook (woorkbook2.xls)
Does anybody know why this is happening??
Thanks in advance for your time,
A

 

[Joel]

You probably put a reference into the workbook that included the file name in
the original workbook. Eliminate all the reference that include workbook
like [book1.xls]sheet1!A1:B10. Instead just use sheet1!A1:B10

Try the following to shep solve the problem
1) Do a FIND (worksheet menu Edit find) for the workbookname on all the
worksheets.
2) Look at names (worksheet menu Insert - Name - Define) and look for any
refernces that include the workbook.
3) Check for additional reference in worksheet menu File - Proerties - Custom.

You may need to fix your macro(s) so they don't create references to the
workbook.

 

[alan82]

What is the code you use to assign macros to the menu items?
What version of Excel are you using?
--
Jim Cone
Portland, Oregon  USA

"alan82" <[email protected]>
wrote in message
Hi all,
I have created my own menu for an Excel workbook using VBA set to run
when the Workbook is opened.
All the menu's work ok and call the correct macros until I save the
Workbook using a new name (workbook2.xls instead of workbook1.xls).
After I've done this, when selecting a menu item Excel displays and
error message saying that it cannot locate the original workbook
(workbook1.xls) and gives me no options to update a link.
All the macros are saved in the new workbook (woorkbook2.xls)
Does anybody know why this is happening??
Thanks in advance for your time,
A

Excel 2003 SP3

Code:

Sub AddMenus()

Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("BM
TP").Delete
On Error GoTo 0

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpMenu = cbMainMenuBar.Controls("Help").Index

Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
Before:=iHelpMenu)
cbcCutomMenu.Caption = "BM TP"

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Populate"
.OnAction = "Populate"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Archive"
.OnAction = "Archive"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Hide Rows"
.OnAction = "HideRows"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Unhide Rows"
.OnAction = "UnhideRows"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Add Headers"
.OnAction = "AddHeaders"
End With

End Sub

Thanks,

 

[Jim Cone]

Change the .OnAction code lines to this structure...
.OnAction = ThisWorkbook.Name & "!Populate"
--
Jim Cone
Portland, Oregon USA

What is the code you use to assign macros to the menu items?
What version of Excel are you using?
--
Jim Cone
Portland, Oregon USA

"alan82" <[email protected]>
wrote in message
Hi all,
I have created my own menu for an Excel workbook using VBA set to run
when the Workbook is opened.
All the menu's work ok and call the correct macros until I save the
Workbook using a new name (workbook2.xls instead of workbook1.xls).
After I've done this, when selecting a menu item Excel displays and
error message saying that it cannot locate the original workbook
(workbook1.xls) and gives me no options to update a link.
All the macros are saved in the new workbook (woorkbook2.xls)
Does anybody know why this is happening??
Thanks in advance for your time,
A

Excel 2003 SP3

Code:

Sub AddMenus()

Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("BM
TP").Delete
On Error GoTo 0

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpMenu = cbMainMenuBar.Controls("Help").Index

Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
Before:=iHelpMenu)
cbcCutomMenu.Caption = "BM TP"

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Populate"
.OnAction = "Populate"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Archive"
.OnAction = "Archive"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Hide Rows"
.OnAction = "HideRows"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Unhide Rows"
.OnAction = "UnhideRows"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Add Headers"
.OnAction = "AddHeaders"
End With

End Sub

Thanks,