Custom menu (Office 2002)

[Mike_R]

I think the best idea for you here would be to make use of the Workbook_Activate() and Workbook_Deactivate() Events.

You will still need to create your Menus within the Workbook_Open() Event, but when you use Controls.Add() make sure that you utilize the 'Temporary' parameter, setting it = True. This way you will not have to worry about deleting the Control when you Workbook closes, for when Excel Quits, these temporary controls will not be present the next time Excel is opened (unless explicitly created again, of course).

You may also need to pick up if your Control already exists due to another Workbook having already created it. In this case, you would not call Controls.Add(), but would simply Set a variable to the Control in question so that you can set it's .Visible property as required.

Within the Workbook_Activate() and Workbook_Deactivate() Events I would set the .Visible property of this Control to True and False, respectively.

Another thought is to toggle it's .Enabled property, so that you can see where it is all the time, but grayed out and unusable.

That's about it, I hope this was clear!

-- Mike