Custom menu (Office 2002)

[Insomniac]

Dennis, I would maybe use Application level event trapping & check for a specific sheet & value in a cell, or just rely on each Workbook Activate event to activate the addin. See what OP requires 1st (damn, too many experts here, LOL)

[Mike_R]

Hi Insomnic :hi

LOL, there are too many Experts! Until a few minutes ago, I thought that I had some pretty good code, Doh!

Ok, I'll address these points one by one, but it does sound like that to handle this for XL'97 that Application Trapping would be required, as you noted.

(1) "Dim WithEvents m_cbButton As Office.CommandBarButton" will fail miserably for users of older versions: ie 97.

I was blissfully unaware of this! I don't know how you XL'97 get around in life... LOL. Hmm... this seems to necessitate Application Event Trapping. Interesting that XL'97 supports Application Events but not not CommandBar Events, eh?

(2) If the user resets the toolbars after Workbook is opened you will get errors with m_cbButton.Visible = True, etc...

This happens if you edit your code within the Project, forcing your project to "Lose State". Once your code is finalized, I would expect that this would not occur. But since trapping Application Events would completely separate the "Event Trapping" from the Code within the Workbook in question, it would again seem to be a better solution. Two points for the sleepless one...


(3) Mike with your code only the creating Workbook can run the code. If ThisWorkbook Is ActiveWorkbook Then...

This part was 100% intentional. It seemed from the question that the OP intends for his code to fire for the ActiveWorkbook only. With possibly multiple Workbooks all hooking to this Event, we need to see which Workbook is Active and only fire for this one. But yes, yet again, this would be easier for Application Events, where we can simply Trap the Application_WorkbookActivate() Event. Three points... Hmm...

5. Personally I would make an Addin for the control and check for it's existence in the Workbook_Activate() / Workbook_Deactivate() events,
I would place all the code in the addin to do enable, delete etc.

I stayed away from this for one reason and one reason only, that this involves an installation issue to some degree. With the current solution, you could email this Workbook to another User and they could open it like any other Workbook and it will run.

Using an XLA requires that the XLA be placed into the XLStart directory, or perhaps placed within the same folder as your Workbook and the Workbook_Open() event would then have to search out for the XLA, find it, and open it. Not a massive hassle, but, well, I decided to go for a more "self contained" solution.

That said, I had no idea that the code I provided would not work at all in XL'97. Soooo.... hmmmm... I guess we should make a version using Application Event Trapping, eh?

I don't have the time today, but if you want to take a crack at it, be my guest Otherwise, I'll take a shot at it tomorrow if no one else has provided a solution by then. Gotta run...

Have a great night all ,
Mike

[Insomniac]

Mike, thats a pretty good summary of the points I made & as you know I always allow for versions down to xl97. Maybe we overcomplicating the theme of original post but hopefully will stimulate others to consider all the variations on the theme. Me too need to review tommorrow, hopefully more input from many other experts available here.

[XL-Dennis]

Hi guys,

Don?t forget that the OP is only running Excel 2002

Kind regards,
Dennis

[Insomniac]

Yes Dennis thats why I said "Maybe we overcomplicating the theme of original post" wait for response I think.

[Sphinx No. 4]

I just got home from work, at 11:30 PM , and wednesday is conference day, but I will have a go as soon as possible.

Thanks for all the replies, I am truly overwhelmed! :hi

[shades]

This may be way off in left field... but...

Not knowing the context of the OP and him using XL2002, I wonder if scenarios might be easier to implement on one sheet, or even custom views, and then have a macro assigned to buttons that activate the view/scenario?