VB to clear any filters when closing a spreadsheet
 

Hi

I want to enter some vb to a Workbook_BeforeClose option that automatically
clears any filters in a specific sheet as the workbook is closed. Example of
recorded code is below which works but I have the sheet protected so I do not
believe below code is optimal for a protected sheet. Is there simple code to
simply clear any filters?

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter
--
Kevin

VB to clear any filters when closing a spreadsheet
 

I wouldn't put this into the _beforeclose event.

If you do put it there, then the user still has to save the workbook after that
change. And if the user didn't want to save, the filter wouldn't be cleared.

If you added a Save to your procedure, then it may mess up the workbook--if the
user opens the workbook and deletes 18 worksheets and wants to close without
saving, your code would mess that up.

Instead of clearing the filter when closing the workbook, I'd clear it when it
was opened:


If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
If .FilterMode Then
.ShowAllData
End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Kevin wrote:

Hi

I want to enter some vb to a Workbook_BeforeClose option that automatically
clears any filters in a specific sheet as the workbook is closed. Example of
recorded code is below which works but I have the sheet protected so I do not
believe below code is optimal for a protected sheet. Is there simple code to
simply clear any filters?

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter
--
Kevin

--

Dave Peterson

VB to clear any filters when closing a spreadsheet
 

how about worksheet.protection = false
then remove your filter, and turn the protection back on.

Having said that, if you manage to turn the filter on with the applied
protection, the protection should not stop you removing it......




"Kevin" wrote:

Hi

I want to enter some vb to a Workbook_BeforeClose option that automatically
clears any filters in a specific sheet as the workbook is closed. Example of
recorded code is below which works but I have the sheet protected so I do not
believe below code is optimal for a protected sheet. Is there simple code to
simply clear any filters?

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter
--
Kevin

VB to clear any filters when closing a spreadsheet
 

Try this:
Sub clearfilter()
If ActiveSheet.AutoFilterMode Then
ActiveSheet.Unprotect
Selection.AutoFilter
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, userinterfaceonly:=True
End If
End Sub

Regards,
Stefi


€žKevin€� ezt Ã*rta:

Hi

I want to enter some vb to a Workbook_BeforeClose option that automatically
clears any filters in a specific sheet as the workbook is closed. Example of
recorded code is below which works but I have the sheet protected so I do not
believe below code is optimal for a protected sheet. Is there simple code to
simply clear any filters?

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter
--
Kevin

VB to clear any filters when closing a spreadsheet
 

not entirly correct. After removing the filter, you can force a save
ThisWorkbook.Saved = True
Save
ThisWorkbook.Close

rdwj

"Dave Peterson" wrote:

I wouldn't put this into the _beforeclose event.

If you do put it there, then the user still has to save the workbook after that
change. And if the user didn't want to save, the filter wouldn't be cleared.

If you added a Save to your procedure, then it may mess up the workbook--if the
user opens the workbook and deletes 18 worksheets and wants to close without
saving, your code would mess that up.

Instead of clearing the filter when closing the workbook, I'd clear it when it
was opened:


If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
If .FilterMode Then
.ShowAllData
End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Kevin wrote:

Hi

I want to enter some vb to a Workbook_BeforeClose option that automatically
clears any filters in a specific sheet as the workbook is closed. Example of
recorded code is below which works but I have the sheet protected so I do not
believe below code is optimal for a protected sheet. Is there simple code to
simply clear any filters?

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter
--
Kevin

--

Dave Peterson

VB to clear any filters when closing a spreadsheet
 

And if I deleted those 18 worksheets in error, then the worksheet could be saved
in a ruined state.

rdwj wrote:

not entirly correct. After removing the filter, you can force a save
ThisWorkbook.Saved = True
Save
ThisWorkbook.Close

rdwj

"Dave Peterson" wrote:

I wouldn't put this into the _beforeclose event.

If you do put it there, then the user still has to save the workbook after that
change. And if the user didn't want to save, the filter wouldn't be cleared.

If you added a Save to your procedure, then it may mess up the workbook--if the
user opens the workbook and deletes 18 worksheets and wants to close without
saving, your code would mess that up.

Instead of clearing the filter when closing the workbook, I'd clear it when it
was opened:


If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
If .FilterMode Then
.ShowAllData
End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Kevin wrote:

Hi

I want to enter some vb to a Workbook_BeforeClose option that automatically
clears any filters in a specific sheet as the workbook is closed. Example of
recorded code is below which works but I have the sheet protected so I do not
believe below code is optimal for a protected sheet. Is there simple code to
simply clear any filters?

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter
--
Kevin

--

Dave Peterson


--

Dave Peterson

VB to clear any filters when closing a spreadsheet
 

yeap - and you can catch that in the BeforeClose as well. If you check that
thisworkbook saved = true before running the whole thing. In the end - it is
the user that gives the command to close, so I find your comment close to
bull****.

rdwj.

"Dave Peterson" wrote:

And if I deleted those 18 worksheets in error, then the worksheet could be saved
in a ruined state.

rdwj wrote:

not entirly correct. After removing the filter, you can force a save
ThisWorkbook.Saved = True
Save
ThisWorkbook.Close

rdwj

"Dave Peterson" wrote:

I wouldn't put this into the _beforeclose event.

If you do put it there, then the user still has to save the workbook after that
change. And if the user didn't want to save, the filter wouldn't be cleared.

If you added a Save to your procedure, then it may mess up the workbook--if the
user opens the workbook and deletes 18 worksheets and wants to close without
saving, your code would mess that up.

Instead of clearing the filter when closing the workbook, I'd clear it when it
was opened:


If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
If .FilterMode Then
.ShowAllData
End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Kevin wrote:

Hi

I want to enter some vb to a Workbook_BeforeClose option that automatically
clears any filters in a specific sheet as the workbook is closed. Example of
recorded code is below which works but I have the sheet protected so I do not
believe below code is optimal for a protected sheet. Is there simple code to
simply clear any filters?

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter
--
Kevin

--

Dave Peterson


--

Dave Peterson

VB to clear any filters when closing a spreadsheet
 

i put the code in both open and close

ie .enable filter

works

thanks
--
Kevin


"Dave Peterson" wrote:

And if I deleted those 18 worksheets in error, then the worksheet could be saved
in a ruined state.

rdwj wrote:

not entirly correct. After removing the filter, you can force a save
ThisWorkbook.Saved = True
Save
ThisWorkbook.Close

rdwj

"Dave Peterson" wrote:

I wouldn't put this into the _beforeclose event.

If you do put it there, then the user still has to save the workbook after that
change. And if the user didn't want to save, the filter wouldn't be cleared.

If you added a Save to your procedure, then it may mess up the workbook--if the
user opens the workbook and deletes 18 worksheets and wants to close without
saving, your code would mess that up.

Instead of clearing the filter when closing the workbook, I'd clear it when it
was opened:


If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
If .FilterMode Then
.ShowAllData
End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Kevin wrote:

Hi

I want to enter some vb to a Workbook_BeforeClose option that automatically
clears any filters in a specific sheet as the workbook is closed. Example of
recorded code is below which works but I have the sheet protected so I do not
believe below code is optimal for a protected sheet. Is there simple code to
simply clear any filters?

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter
--
Kevin

--

Dave Peterson


--

Dave Peterson

VB to clear any filters when closing a spreadsheet
 

So how do you restore those 18 deleted worksheets and make sure the filters are
cleared?

I don't think you can.

rdwj wrote:

yeap - and you can catch that in the BeforeClose as well. If you check that
thisworkbook saved = true before running the whole thing. In the end - it is
the user that gives the command to close, so I find your comment close to
<<snipped