prevent user from saving file when data is filtered

[Corleone]

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

 

[Corleone]

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]

This line of code would reset the Autofilter so that everything is displayed and leave the Autofilter mode turned on.

If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData

 

[AlphaFrog]

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]

This line of code would reset the Autofilter so that everything is displayed and leave the Autofilter mode turned on.

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