PDA

View Full Version : Solved: Return Autofilter to All upon sheet deactivation



belly0fdesir
02-01-2006, 02:09 PM
:hi:

This is probably an easy one. I've searched through the KB, because I'm trying to learn this on my own, but haven't found what I'm looking for. I found one that autofilters based on color, and have tried to build off of that, but was unsuccessful. So, can someone help me with this one. I have an Autofilter set up on one column of a sheet, so that users can isolate cells, however, if they leave the sheet Autofiltered, then another macro I have running will not function properly. :dunno

Is there a way to have the autofilter return to All, but not turn off, upon sheet deactivation (changing to another sheet)?

mdmackillop
02-01-2006, 02:23 PM
Put this into the ThisWorkbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
On Error Resume Next
Sh.ShowAllData
End Sub

belly0fdesir
02-01-2006, 03:40 PM
I figured it would be that simple, however I entered the code as instructed, and it still didn't do anything when I switched to a different sheet. I have uploaded a copy of the spreadsheet, zipped up, here (http://s62.yousendit.com/d.aspx?id=28X66N5LAH51S0ACTX0MHRNF1C) so anyone who wants to help and has the time can see what I mean.

mdmackillop
02-01-2006, 03:56 PM
Hi,
The sheet needs to be unprotected for the code to run

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.ScreenUpdating = False
On Error Resume Next
With Sh
.Unprotect
.ShowAllData
.Protect AllowFiltering:=True
End With
Application.ScreenUpdating = True
End Sub

Bob Phillips
02-01-2006, 03:58 PM
Is it the protection?

mdmackillop
02-01-2006, 04:03 PM
BTW, You could simplify your other code by looping through your array of sheet names
eg

For Each s In Sh
Sheets(s).Rows("7:121").Sort Key1:=Sheets(s).Range("b7"), Header:=xlNo
Next

belly0fdesir
02-01-2006, 04:59 PM
Thank you very much.. that was it... so simple... thank you. I will simplify my other code as well to minimize clutter...

One more thing, since you're so freaking brilliant and you've dled my spreadsheet and all. This might be asking a lot, but, throughout the whole spreadsheet I have a few strange workarounds and on the tab called "Dept Calendar", the user is supposed to click on the dropdown for a particular date of the month and see the names that are out for that date. There are a few hidden formulas and cells for this to happen. However, I can't figure out a way for it not to show the empty cells in the validation list. This probably isn't making much sense, so I think I'm just going to say thank you and call this one solved.

You and the other veterans of this site truly are brilliant. I have learned a lot in the short time I've been a member by perusing the forums and KB and I will continue to do so. Thank you again.

mdmackillop
02-01-2006, 05:50 PM
I've had a look at "Complicated" and I see why it's named that. I've not worked out the logic of all these indirects, and without data, it's even harder to follow. I don't think that Data Validation can ignore the blank cells, so you need a different approach. It should be possible to create a Selection Change event which adds the names to a Data Validation list, omitting any blanks. This one dynamic list would be used by all the dropdowns.