PDA

View Full Version : AutoFilter Stopped Working



wadirks
03-15-2006, 07:13 AM
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?

malik641
03-15-2006, 08:23 AM
Is the sheet protected???

By the way, welcome!!

wadirks
03-16-2006, 08:08 AM
The sheet is not protected. I know this because when I choose Tools > Protection the Protect Sheet ... menu option is displayed and enabled.

malik641
03-16-2006, 08:50 AM
Try running this code and see if it comes back:


Sub Hide_Unhide_ComBar()
Application.CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Filter").Controls("AutoFilter").Enabled = True
End Sub

wadirks
03-16-2006, 02:30 PM
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?

lucas
03-16-2006, 02:36 PM
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.

wadirks
03-17-2006, 08:06 AM
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.

lucas
03-17-2006, 09:04 AM
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.

wadirks
03-17-2006, 11:24 AM
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? :banghead: or :dunno your choice!

lucas
03-17-2006, 12:35 PM
Paragraph from my previous post....


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.



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.

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....

mdmackillop
03-17-2006, 01:07 PM
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

lucas
03-17-2006, 01:11 PM
Good idea Malcolm, there are quite a few modules to export and then import.

wadirks
03-20-2006, 11:39 AM
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!

lucas
03-20-2006, 12:18 PM
Well at least you know how to get it back now if it happens again. Glad you got it back in working order.

Ivan F Moala
03-20-2006, 08:21 PM
You needed to reset the Workbooks drawing object

You can do this via code.... or manually



Sub Reset()
ThisWorkbook.DisplayDrawingObjects = xlDisplayShapes
End Sub

mdmackillop
03-21-2006, 05:18 AM
Hi Ivan,
Very useful fix to know.
Regards
MD

lucas
03-22-2006, 08:40 AM
Ivan,
Thats a much nicer solution, could you explain how to do this manually please?

Ivan F Moala
03-23-2006, 01:00 AM
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.

lucas
03-23-2006, 07:51 AM
Thanks for sharing that information. I would never have thought to look in properties for thisWorkbook. Learn something every day here.