Excel Auto Filter Function

[Guest]

On my spread sheet I am using the Auto Filter and it works ok. My problem is
the spread sheet is shared on a LAN my all the office personnel use it and no
one ever puts all the columns back to the sort "All" default. Is there a way
to make the spread sheet when its closed reset all columns to the "All"
default so the next person doesn't have to make all the corrections to return
the sheet to it's normal view.

Thanks - George

 

[Guest]

Add this to the Workbook Open code:


Private Sub Workbook_Open()
Sheets("sheetname").Select
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
End Sub


Replace sheetname with the name of your worksheet containing the AutoFilter.
And add as many lines as necessary for each column that is filtered.

HTH,
Elkar

 

[Guest]

Thanks - I just need to figure out the workbook open code

George

 

[Dave Peterson]

One way:

Option Explicit
Sub auto_open()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ThisWorkbook.Worksheets
wks.ShowAllData
Next wks
On Error GoTo 0
End Sub

or if you like checking first...

Option Explicit
Sub auto_open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If wks.AutoFilterMode Then
If wks.FilterMode Then
wks.ShowAllData
End If
End If
Next wks
End Sub

 

[Dave Peterson]

Ps. I used the Auto_open subroutine.

If you reset the filter when they were closing the workbook, then you'd have to
save it in that state. And if the user opened the workbook and did a lot of
damage and wanted to close without saving, your code that did the save could
really make you sad.

 

[Guest]

When your workbook is open, hit Alt-F11 to open the Visual Basic Editor.

On the left side of the screen, you should see a "Project Window". In this
window you should find an item called "ThisWorkbook" with a little Excel
Worksheet icon next to it. Double click this.

Now you should have a new window open on the right side of the screen called
"WorkbookName - ThisWorkbook (Code)"

Paste the code below in that window. Modify as needed. Then close the
Visual Basic Editor and save your workbook. Now, hopefully, it should clear
any criteria out of the AutoFilter whenever the workbook is opened.