Question on Removal of Column Filtering Upon Closing of Spreadsheet

[golf4]

Hi, everyone -

I have a quick question regarding a detailed spreadsheet that I have
created. My spreadsheet track vacancy days for a local housing agency.
Data entry field include aspects like Vacate Date, Re-Rent Date, Work
Order Created Date, Specific Program, Caseworker Name, etc.

Right now, I have turned on the Auto-Filter on all of the columns so
that we can filter out vacated units by program, by caseworker, etc.
The problem that we're having is that, once an employee does some
filtering and saves the spreadsheet, the next time it's opened the
filter is still filtering the same data. Employees that are not,
necessarily familiar with Excel, can't really tell all of the units
that are included on the spreadsheet unless they "unfilter" the
column(s).

I was wondering whether someone could help with some VB code that,
upon saving or closing the spreadsheet, the filtering will be removed
from all of the columns but won't turn off the Auto-Filter? If it will
help, I would, certainly, be willing to forward my spreadsheet if it
will provide clarification on what I'm talking about.

Thanks, again, for all the help.

Golf

 

[Doug Glancy]

Golf,

It seems to me the easiest thing is to write code that turns filtering off
and on before closing or saving. To do so, I'd paste this code into the
ThisWorkBook module in the VBE:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Sheet1").UsedRange.AutoFilter
Worksheets("Sheet1").UsedRange.AutoFilter
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Worksheets("Sheet1").UsedRange.AutoFilter
Worksheets("Sheet1").UsedRange.AutoFilter
End Sub

hth,

Doug

 

[Tom Ogilvy]

Doug,

Any change you make must be saved to be "in effect" when the workbook is
opened. It seems you should put a save command in the beforeclose after you
make your settings. Then you would not need to duplicate the code in the
before save.

 

[Doug Glancy]

Tom,

On my machine with xl2000, win xp, I don't need to save before closing for
it to work. As soon as I close the book, it turns off autofiltering, turns
it back on, and then asks whether to save. Even if not saved, when opened
back up filtering is on but no filters are set, which I think is what Golf
wanted.

Also, I inlcuded the before save because Golf asked for the filter to be
reset upon saving or closing.

Let me know if I'm not understanding you.

Thanks,

Doug

 

[Tom Ogilvy]

Doug,
Open a new workbook. Save it.

In A1, Enter the Value Doug
close the workbook without saving

Open it up.

Is the word Doug in A1.

It isn't.

Same principle for Filtering. I don't know what you are observing, but if
you don't save the workbook, any changes made, include filter settings, will
not be saved.

Let me know what basic concepts about saving files you don't understand.

I believe you understood what Golf wanted - your solution would work if the
user elects to save the workbook when prompted - however, the root of the
problem is that the users are not behaving as is, so the approach, as
offered, would be self defeating from the start.

 

[Doug Glancy]

Tom,

You are of course right. I was making a dumb mistake, which made me think
Filter settings were somehow different when in regards to saving.

Thanks for your persistence.

So, Golf, I believe this does what you want, but if not, I'm sure we'll hear
about it!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Sheet1").UsedRange.AutoFilter
Worksheets("Sheet1").UsedRange.AutoFilter
ThisWorkbook.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Worksheets("Sheet1").UsedRange.AutoFilter
Worksheets("Sheet1").UsedRange.AutoFilter
End Sub

hth,

Doug

 

[golf4]

Hi, Doug & Tom -

Thanks for responding so quickly to my query. I hope it is clear that
it was not my intention to cause any "heated" discussions with my
question, but we all need that extra excitement on a Friday --- keeps
the blood flowing!!!

I've attempted the suggestions, and this is what I found happening:
(after adding the code)I filtered one column, saved the spreadsheet
with the filtering on, closed it and reopened. It appeared that, once
reopened, the filtering in the column would switch off. If any other
column was filtered, saved and closed, then reopened, the filtering
would not clear. Plus what else is happening is that the freeze-frame
I had in the spreadsheet would really mess up and you could
right-scoll down without changing the appearance of the spreadsheet on
the monitor.

I'm wondering if it would be possible for me to forward a copy of the
spreadsheet to one of you guys for additional help on this issue?

Thanks again for all the assistance.

Golf

 

[Dave Peterson]

If you fix up the workbook when it's opened, it might be easier:

In a general module,

If you want to just show all the data (keep the dropdown arrows):

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


If you want to eliminate the dropdown arrows completely:

Option Explicit
Sub auto_open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
wks.AutoFilterMode = False
Next wks
End Sub

 

[golf4]

Hi, guys -

Just wanted to post a thank-you note for all the help. It's fantastic
to be able to post queries here and obtain so much valuable
assistance.

Thanks again.

Golf