I have a spreadsheet I use for project scheduling. I have been using the AutoFilter feature since I set this workbook up several months ago. However, a couple of weeks ago the AutoFilter menu option became disabled (grayed out). I do not know why nor how to enable it. Can someone help me get that feature enabled?
Is the sheet protected???
By the way, welcome!!
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
The sheet is not protected. I know this because when I choose Tools > Protection the Protect Sheet ... menu option is displayed and enabled.
Try running this code and see if it comes back:
[VBA]
Sub Hide_Unhide_ComBar()
Application.CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Filter").Controls("AutoFilter").Enabled = True
End Sub
[/VBA]
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
Thank you for your time. Your suggested code did not do the trick. AutoFilter is still disabled on every sheet in this workbook. Other workbooks work fine. Any other suggestions?
If its not too big could you remove any sensitive data and post it here? Sometimes its easier to understand if you have it in front of you.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Sure and thanks, I removed some data and zip the file to make it smaller, hope everything still works. Also hope I did the attachment correct.
Ok, the good news is I got your autofilter feature to light back up. Bad news is I think its caused by one of the many macro's in your workbook.
What I finally wound up doing was copying all of the sheets in the workbook to a new workbook. Now I have a workbook with all of your sheets but none of your macro's and the autofilter feature lights up ok.
Someone, I'm guessing you, will have to add each of the modules back to the copy one at a time and run each one to see which is the offender. Then maybe we can figure out what its doing and at the end of the procedure undo it. Hope that makes sense.
Sorry, but thats all I could figure out to do. I have attached the copy without the macros.
If you can figure out which macro(and its possible that more than one are guilty) is the culprit and need help getting it to work correctly, don't hesitate to post back here.
I'm not the expert that some of these guys are but this is my best guess. Maybe someone else will come along and see something I missed. By the way, my norton didn't find any malicious code so I'm fairly sure its not virus related. I hope this gets you headed in the right direction.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I uploaded the workbook you removed the macros from. Sure enough AutoFilter was enabled. I then deleted all of the macros from the same file I sent you, the one you worked with before you worked with it. AutoFilter did not enable.
I then went to my production workbook and deleted all of the macros. AutoFilter did not enable.
How did you get it to work and I cannot?or
your choice!
Paragraph from my previous post....
Use control+left click to select all of the tabs, then right click and copy to a new workbook. Take your time doing this and you can copy them to a new workbook and name and save it.Originally Posted by Steve
My guess is that the macros have affected the workbook but removing the macros does not change the effect they have had on the workbook so you need to copy the sheets to a new workbook and start over adding macro's one at a time and try each one before you add another one and be sure to check your autofilter before moving to the next macro.
you can export your macro's as .bas files from the vbe by right clicking on them and selecting export....
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I tried the same as Lucas.
Add a sheet to your original workbook, select all sheets except the new one and move to a new book. With the VBE, drag each of your modules into the new book project, no need to export, and things (for me at least) were working again.
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Good idea Malcolm, there are quite a few modules to export and then import.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I did as everyone suggested, copying the sheets, moving each macro, running each macro and checking AutoFilter menu option after each run. Guess what! AutoFilter is still enabled and none of the macro turned it off. So, what turned it off? Who cares, it's working!! Thanks everyone!
Well at least you know how to get it back now if it happens again. Glad you got it back in working order.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
You needed to reset the Workbooks drawing object
You can do this via code.... or manually
Sub Reset() ThisWorkbook.DisplayDrawingObjects = xlDisplayShapes End Sub
Kind Regards,
Ivan F Moala From the City of Sails
Hi Ivan,
Very useful fix to know.
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Ivan,
Thats a much nicer solution, could you explain how to do this manually please?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
That property is a part of the ThisWorkbook object .......
While in the VBE, navigate to the ThisWorkbook object.
View its properties, one of which is [DisplayDrawingObjects]
This is where you change it.
Kind Regards,
Ivan F Moala From the City of Sails
Thanks for sharing that information. I would never have thought to look in properties for thisWorkbook. Learn something every day here.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Posting Permissions
Reply With Quote
