User creates new row while autofilter is on

[BarcelonaJoe]

Hello Excel Experts,

I am new to the forum. Anyway, I have a hard time describing this issue so I'm not sure if it has been posted previously. If you know of a thread, please let me know.

We currently have a workbook where the users can employ selections via column filters. They would use the filters to manage their entries.

They get in trouble when they forget to reset their selections and then add a new row/s. So instead of the next true row being 255, the next row they add might start at 4834. So at times, we get frantic calls about "missing rows" or "duplicate rows" by virtue of the filter selections being on or defaulted.

We are trying to help our users by somehow detecting the filter settings and maybe using an on change event to either warn the user that the filter is being used, or do something else to make sure that the next row added after 254 is 255.

I got a year of VBA experience and 2 .XLSM 2007 workbooks in production to my name. Unfortunately, I can't seem to see a solution for this. I reason that this is a chicken and egg situation--by the time the on change event occurs, the damage has been done (meaning that the row at 4834 already exists).

Anyway, any thoughts would be appreciated.

Thanks,

BarcelonaJoe

 

[Jaafar Tribak]

Hi and welcome to the board.

Have you considered using the Application.Undo Method in the worksheet change event if the filter is being used?

 

[BarcelonaJoe]

Thanks for your thoughts, Jaafar.

I will definitely look into it.

BarcelonaJoe

 

[Taul]

If it is just a visual reminder they need (doesn't stop them doing it)
you could add a command button to the page that changes colour to red when a filter is on, clicking the button will turn all filters off and turn the button green.

Like I said, it is only a visual reminder.

Private Sub Worksheet_Calculate() 'filter button colours & caption
If ActiveSheet.FilterMode Then
CommandButton1.Caption = "Turn Filters OFF"
CommandButton1.BackColor = RGB(252, 72, 72) 'red
Else:
CommandButton1.Caption = "Filters ok"
CommandButton1.BackColor = RGB(192, 255, 192) 'light green
End If
End Sub

Private Sub CommandButton1_Click() 'Filter Button Re-Set
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
ActiveSheet.Range("A6").Select 'park the cursor
End Sub

 

[Taul]

PS - with worksheet calculate(),
you do need to have a calculation on the worksheet, otherwise it will not function

 

[BarcelonaJoe]

Thank you, Paul.

A visual cue might work. I will investigate it.

BarcelonaJoe

 

[Taul]

You could also use two formulas provided one of your columns contains values.

This would give you the total for the range

=SUM(A2:A100)

This would give you the Filtered total.

=SUBTOTAL(109,A2:A100)

Both totals will be the same until you activate a filter. So you could combine these two in an “IF” statement:-

=IF(SUM(A2:A100)=SUBTOTAL(109,A2:A100),"Filters are ok","Filters are ON")

Then use conditional formatting to check for “Filters are ON” and select a bright (in ya face) colour.

.

 

[BarcelonaJoe]

Thanks for the thought, Paul.

Based on your input, I am able to generate a warning for the user when any change is detected while the autofilter selections are on.

My idea is to warn the user early, so that he/she can undo the recent entry, reset the filter, and then continue on with the edit at the next row where the insertion is more appropriate.

I was thinking about automating the undo, but it is much safer to let the user do it.

So things are wrapping up quite nicely. Thanks to Paul and Jaafar for their valuable assistance.

BarcelonaJoe