enabling a custom menu at workbook open that is disabled
 

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.

enabling a custom menu at workbook open that is disabled
 

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


wrote in message
oups.com...
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.

enabling a custom menu at workbook open that is disabled
 

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




"Chip Pearson" wrote in message
...
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


wrote in message
oups.com...
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.

enabling a custom menu at workbook open that is disabled
 

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


Chip Pearson wrote:
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