Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to clear all filters in worksheet for all columns | Excel Discussion (Misc queries) | |||
How can I clear all cells containing #n/a on a spreadsheet? | Excel Worksheet Functions | |||
Closing Excel spreadsheet | Excel Discussion (Misc queries) | |||
CLEAR FIND BUFFER UPON CLOSING | Excel Worksheet Functions | |||
Dialog box when closing an unsaved spreadsheet | Excel Discussion (Misc queries) |