Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2006

Tip: Looking for answers? Try searching our database.

enabling a custom menu at workbook open that is disabled

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
mmmbert@yahoo.com - 31 May 2006 20:58 GMT
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 - 31 May 2006 22:24 GMT
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.

Signature

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

> In an add-in I have created, a custom menu item is created, it
> is
[quoted text clipped - 18 lines]
> I haven't been able to figure it out.  Any help would be
> appreciated.
Chip Pearson - 31 May 2006 22:29 GMT
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.

Signature

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

> Take your code out of Workbook_Open and put it in a procedure
> called Auto_Open in a regular code module, not the ThisWorkbook
[quoted text clipped - 23 lines]
>> I haven't been able to figure it out.  Any help would be
>> appreciated.
mmmbert@yahoo.com - 31 May 2006 22:33 GMT
Thanks, that did the trick.  I was hoping to avoid the enable macros
dialog, but I can live with it.

> Take your code out of Workbook_Open and put it in a procedure
> called Auto_Open in a regular code module, not the ThisWorkbook
[quoted text clipped - 6 lines]
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.