Disabled Toolbar Buttons
About a week ago I was preparing to answer a question about External Data on the newsgroups. I felt that I knew the answer, but I like to test it out before making a fool of myself. I opened a workbook that had an External Data table in it to find that the Edit Query toolbar button was disabled. I did a little searching on Google and found some people with similar questions, but no real answers.
I should be able to solve this, so I set to work. I opened a few more workbooks with the same result. I created a new query table in a new workbook, no dice. I tried different DSN’s, different databases, different workbooks, all showed the Edit Query button disabled.
I don’t need to edit my queries very often, so this wasn’t a pressing matter (the newsgroup poster might disagree). So I put the problem on the back burner, knowing that I was missing something really stupid and that it would come to me. Nothing came.
I asked some fellow MVPs and the answer came back in about 5 seconds: The toolbar button was accidentally disabled and you just need to re-enable it. I went to the Immediate Window and typed
Application.CommandBars(”External Data”).Controls(1).Enabled = True
I could have also just Reset the CommandBar with the same result. How that toolbar button became disabled in the first place is a mystery. I do a lot of experimenting in Excel, but I think I would remember messing around with that toolbar. It works now, and I’m happy about that. I’m not happy that I can’t remember all the obvious things to try before I ask for help. Now that I’ve written this post, it should stick in my memory.
Jon Peltier:
Dick -
Those .xlb toolbar config files have given me fits in the past, so I avoid relying on them. All of my menu and command bar customizations are repeated by my personal.xls file whenever I fire up Excel, and when I quit, they delete all of the custom menus and commandbars and reset all of the built in toolbars. I use my own modification of J-Walk’s MenuMakr tool, and it never gives me any problem that I didn’t cause by my own typing skills.
This way, the .xlb stays small, and I never rely on anything in it. If you had some program disable that button (or maybe a stray cosmic ray blasted that magnetic particle on your hard drive), your .xlb would happily remember that setting, until you either fixed it, or a lightning strike at a distribution transformer reset something else.
- Jon
26 August 2004, 1:08 pmJon Peltier:
Today I was chugging away, and went to use a filter, and the commands on the Data > Filter submenu were disabled. Weird. Validation, subtotals, and a few assorted items on other menus. Then I noticed Save As and all the Print and Page Setup items were disabled on the File menu. I’d hobbled the File menu to protect a client’s proprietary workbooks, then he played with the add-in himself, and the effect was to randomly turn off menu items. Every time you opened a workbook with its own Workbook_Open procedure, something else was turned off.
I still haven’t fixed it, that’s a job for tomorrow morning.
- Jon
30 August 2004, 10:31 pmNick:
I had this same problem, but I had to set
ActiveSheet.QueryTables(1).EnableEditing = True
To make it work. I vaguely recall setting it False while I was messing around.
26 July 2005, 11:45 pmXinhang:
I have some questions about enable or disable commandbar.
I am working on an Excel by using VBA to protect the worksheet. But after I used VBA code to protect the worksheet, it alse disable my “Paste” function. How can I possibly enable it?
Really appreciate it!
13 October 2005, 9:52 amBalintN:
Hi,
I have something similar, but mine was more weird.
My code disabled Paste (all the Commandbar controls I could find for Paste). But when I ran the opposite, trying to re-enable them, nothing happened.
I tried the immediate window line you suggested above, for one of those buttons (standard toolbar, paste button):
Application.CommandBars(”Standard”).Controls(”&Paste”).Enabled=True
had no effect whatsover.
?Application.CommandBars(”Standard”).Controls(”&Paste”).Enabled=True
resulted False (saying it is disabled).
Then, suddenly all my Paste controls came back - so I started experimenting when it happened, and it turned out it had nothing to do with my code: in one case opening Internet Explorer was the trigger (?) after which the paste buttons came back to life again.
I’d also like to note, that in the meantime I did the same thing for the Sort button, that was working properly (was disabled/enabled as it was supposed to be) all the time (even while I was experimenting with the problems of the Paste buttons!)
The trigger for going wrong seems to have been the code hitting an error. Before that, I tested Paste enable/disable, and it worked like a dream. After hitting the error, the enable stopped working. And I just could not do a straight repro…
So I just stopped trying, and forgot about disabling it
Regards,
10 April 2008, 10:18 amB
Bobbie:
I have recently upgraded to Excel 2007. Spreadsheets that I have attached to the database SPs have “subtotals” button disabled.
1 August 2008, 6:20 amI spent too much time on it already so maybe somebody ran into the same problem. Any help will be appreciated.