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.
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
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
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.
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!
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,
B
I have recently upgraded to Excel 2007. Spreadsheets that I have attached to the database SPs have “subtotals” button disabled.
I spent too much time on it already so maybe somebody ran into the same problem. Any help will be appreciated.
THANK YOU
You may want to check if you have protected the worksheet. Excel disables the buttons on the bars like the subtotals one when you protect a worksheet, it even disables the buttons on those cells that are not locked.
I opened a sheet where the Filter option was disabled. And after that any file that I open the “AutoFilter” option is disabled. I tried few things and doesn’t seem to resolve. Any help here would be appreciated very much.
I have seen several blogs where code is provided but the fact is, I cant use any of’em b’coz in a new file where/why should i write a macro.
All I’m looking for some ways of enabling the “AutoFilter” button from the menu.