Deactivate Custom Menu Controls

[Macro1]

Hi,

I have built a Custom Menu and saved as a .xla which works perfectly, what I'm trying to do now though is grey out (deactivate) certain controls in the menu if a particular sheet isn't open:

So for example as soon as I open Excel the addin runs and populate the menu. I then have code below to fill in the menu options:

Please Login or Register  to view this content.

Is it as simple as wrapping this piece of code with and IF statement or is it something more complex. Either way I'm struggling to write a code that works or find someone on line with a similar problem.

Thanks in advance

Macro1

[pike]

Macro1
Try something like...

Please Login or Register  to view this content.

[Macro1]

Hi Pike,

Thanks for the reply, I was wondering with the code you have given me how these two macros would know which sheet would be open?

Therfore knowing whether the menu options should be active or deactive?

Thanks for your help?

[romperstomper]

You need to monitor application-level events and check workbooks as they open/close/activate/deactivate to see whether your add-ins buttons need to be disabled or not. See Chip Pearson's page here for example.

[Macro1]

Thanks for the link, I have had a read through and I think I understand what its trying to do.

But what I'm still not getting is the where in all this code do I name the spreadsheet for the macro to look out for for this code to operate sucessfully.

Also I have a custom menu bar with about 6 controls, 3 can stay active at all time but the other three must be deactive if a certain spreadsheet isn't open.

Will this work for that?

Cheers

[romperstomper]

Yes, it would work for that. You would need to create a routine that enables or disables those three controls only, then call that from your event code after checking the name of the workbook (which will be passed as an argument to the relevant events). For example, the application level event for a workbook being activated:

Please Login or Register  to view this content.

would pop up a message each time you switched back to the mentioned workbook.

If this only applies to one workbook, is there a particular reason for using an add-in?