Excel Filter Problems

[emers21]

Hi all,

I have a spreadsheet at my school which logs time out data. This is used by all members of staff and is regularly filtered. I have already set up a macro to clear filters when save or close is clicked (people kept closing the sheet and saving with filters which was causing all sorts of problems) However I have a new issue. People keep filtering the sheet, then leaving it open and filtered. The next person will come along and add info with filters on and then it messes up the sheet.

Can anyone think of a way (apart from banning people from using the sheet) of automatically clearing the filters as soon as someone attempts to make an entry. Or blocking anyone from entering any information until they have cleared the filters?

Thanks JE

[LJMetzger]

Hi emers21,

The following Macro which MUST be placed in the Sheet Module may do what you want. If AutoFilter Is on then:
a. UNDO the most recent change
b. Turn AutoFilter Mode OFF
c. Put back the most recent change

Please Login or Register  to view this content.

Lewis

[emers21]

Hi Lewis,

Thanks for looking, unfortunately that doesn't work. Staff need to be able to look at the sheet and filter so I need to leave filter option on the sheet on. However the problem is a member of staff will filter to look at a particular entry then leave the filter on. If they try and save the sheet I have a macro that turns the filter off, if they try and close the sheet I have a macro that turns it off. However I need to write something that stops an entry being made on the sheet until the filters are turned off

Any ideas?

John

[LJMetzger]

Try this. This will turn the filters off, put the entry in, then turn the filter on again, but does NOT save previous filter settings. Previous filter settings can be saved if need be.

You will need to insert the range for your filter and the range for the filter header in the code.

Please Login or Register  to view this content.

Lewis

[LJMetzger]

However I need to write something that stops an entry being made on the sheet until the filters are turned off

The following will:
a. Allow data entry if AutoFilter is OFF.
b. UNDO data entry if AutoFilter is ON, and display a message indicating that 'Data Entry' is NOT allowed until Filters are off.

Lewis

Please Login or Register  to view this content.