Save the dedicated workbook as an add-in as Dave suggests.

Load it through ToolsAdd-ins and it will be available and hidden.


Gord Dibben MS Excel MVP

On Sun, 9 Dec 2007 16:16:01 -0800, Paige
wrote:

Dave, if you use a dedicated workbook, do you have it automatically open when
the original workbook is opened, and just hide it?

"Dave Peterson" wrote:

For something like this -- a dedicated set of macros, I'd use a dedicated
workbook or addin.

I wouldn't clutter up my personal workbook with this.

It would make it easier to share with others, too.

Paige wrote:

Thanks, Dave. At least I know I'm not crazy! I tried moving the
deletion/creation of the toolbar to workbook_activate, which worked, but I
don't think that is the most esthetically pleasing solution, or efficient.
So based upon your input, have decided to put it into the Personal.xls
workbook. That should work. Thanks again to everyone!

"Dave Peterson" wrote:

This may not work for you, but I think it's best to separate the toolbar (and
its macros) into a different workbook (or addin).

Then just one version of the code will be necessary (making updates lots
easier). And each workbook won't try to modify the toolbar again (and again
....)



Paige wrote:

Have a workbook that creates a custom menu when the file is opened; if a user
opens another version of this same workbook, it deletes the menu already
there and recreates it again, to ensure the menu is only on the toolbar once
regardless of the # of workbooks open. Problem is that if I open File#1 and
then File#2, then close File#1, when I use the custom menu to do something,
it re-opens File#1, as if Excel thinks the macros called by the menu still
reside in File#1. I need it to look for the macros in the active workbook.
Have tried numerous things to fix this, but to no avail. The following sub
is called when the workbook is opened:

Sub CreateScheduleMenu()
Dim cmbBar As CommandBar
Dim cmbControl As CommandBarControl

Application.EnableEvents = True
Application.ScreenUpdating = True

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

With ActiveWorkbook
Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True)

With cmbControl
.Caption = "Schedules"
With .Controls.Add(msoControlPopup)
.Caption = "For NCC/Pricer Use Only"
With .Controls.Add(msoControlPopup)
With .Controls.Add(Type:=msoControlButton)
.Caption = "Import from MMS Serv Form"
.OnAction = "GetDataFromMMSForm"
.FaceId = 301
End With
End With
End With
End With
End With
End Sub

--

Dave Peterson


--

Dave Peterson