I've developed 3 programs for a client...they want easy access to th
programs and associated user forms...didn't think it was an
biggy...wroted the code for a custom menu and associated macros...work
fine, well kinda...

when I launch the item selected from the command bar, the program firs
opens up the original workbook I wrote the code in, then goes to th
correct program or user form...

should I delete the custom item and paste the code into each progra
and launch on open, delete on close...I thought writing the code wa
the tuff part! Kinda frustrating since I see it working, just no
directly.

Thanks,
Ro

--
Message posted from http://www.ExcelForum.com

You are exactly correct. You need to have the Add_Toolbar macro in eac
workbook. Auto load the toolbar when the workbook opens. Auto delet
the toolbar when the workbook closes

--
Message posted from http://www.ExcelForum.com

Ron -

When I make a custom menu like this, I always qualify the reference to
the macro with the workbook name:

With Application.CommandBars(i)
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Run the Macro"
.Style = msoButtonCaption
.OnAction = ThisWorkbook.Name & "!RunTheMacro"
End With
End With

This way, the button always refers to the workbook that opened the menu.
The menu is built when the workbook opens, and destroyed when it closes,
so a button never refers to a macro in a closed workbook.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

rju < wrote:

I've developed 3 programs for a client...they want easy access to the
programs and associated user forms...didn't think it was any
biggy...wroted the code for a custom menu and associated macros...works
fine, well kinda...

when I launch the item selected from the command bar, the program first
opens up the original workbook I wrote the code in, then goes to the
correct program or user form...

should I delete the custom item and paste the code into each program
and launch on open, delete on close...I thought writing the code was
the tuff part! Kinda frustrating since I see it working, just not
directly.

Thanks,
Ron


---
Message posted from http://www.ExcelForum.com/

Thanks Ron! That was a valuable addition

--
Message posted from http://www.ExcelForum.com

Answer to my own question:

Here's what I did:

Earlier when I posed my question, the macro I refered to in order t
test code validity was named Delete (how original!)...code as follows:

Sub Delete()
Dim cbWSMenuBar As CommandBar
On Error Resume Next
Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
cbWSMenuBar.Controls("Britannia").Delete
End Sub

the BeforeClose sub now looks like the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Delete
End Sub

Works just fine. Hope this helps someone.

Ro

--
Message posted from http://www.ExcelForum.com