prevent user from saving file when data is filtered

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
717
Hope someone can help

im trying to create a private sub worksheet macro whereby if the user saves the file
it automatically de-selects any filters in the worksheet "SIGNALLING" but whilst
leaving the auto filter on

effectivly going through each column and selecting "all" from the drop down.

i need to have it in as a worksheet macro not a workbook macro, as when i copy the file - i lose the workbook code in the newly created file.

one option offered is to run a code which deletes all the unwanted tabs thus retaining all the code.
the issue with this though is that this can only be done as a "save as" and as it needs to produce 10 different copies containing different worksheets it would mean having to go back into the master file and running a separate macro.


alternativly is there a way i could create a worksheet code which puts up a message in the event of a user trying to save the file whilst their is a filter selected?

thanks
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
717
Ignore the above please

An easier solution for what im trying to achieve would be for a warning message to appear if the user tried copying the data from the "SIGNALLING" Tab in my spreadsheet whilst there is a filter selected.
This would be able to be controlled by a worksheet event code i suspect

can anyone provide a code which would produce a warning message whenever the user tried to copy data from the "SIGNALLING" tab whilst the auto filter had a filter selected?

thanks
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,402
This line of code would reset the Autofilter so that everything is displayed and leave the Autofilter mode turned on.
Code:
If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,402
If you want to save a sheet as a new workbook that includes some macros, create a workbook Template (.xlt) file. Then in your macro, create a new workbook using your template and then copy the sheet to it and save it.
 

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
717
This line of code would reset the Autofilter so that everything is displayed and leave the Autofilter mode turned on.
Code:
If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData

Many thanks for your help this evening AlphaFrog

I have used your filter idea above so thanks again

I will look at whether i can adapt your template idea as well next week

cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top