enabling a custom menu at workbook open that is disabled

[mmmbert]

In an add-in I have created, a custom menu item is created, it is
disabled at the end of the macro that created it with:

CommandBars(1).Controls("blah blah").Enabled = False

I did this because not all workbooks should be able to use it. The
menu is created and greyed out when Excel is started.

In the workbook_open macro of theworkbooks I want the menu to be
available, I placed:

CommandBars(1).Controls("blah blah").Enabled = True

to turn it back on. When the workbook opens, I get:

"Object variable or with block not set"

I haven't been able to figure it out. Any help would be appreciated.

 

[Chip Pearson]

Take your code out of Workbook_Open and put it in a procedure
called Auto_Open in a regular code module, not the ThisWorkbook
module. Sometimes, Excel hasn't fully put itself together when
Workbook_Open runs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

I should have added that you should use the command bar name
rather than number. E.g.,

CommandBars("Worksheet Menu Bar").Controls("blah blah").Enabled =
True

Using the number seems unpredicable. For example,
Debug.Print CommandBars(1).Name
displays "WordArt" for me.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[mmmbert]

Thanks, that did the trick. I was hoping to avoid the enable macros
dialog, but I can live with it.