Check Autofilter on Workbook open

[Peter.Stevens2]

Hi, I've got a problem and I think its one of those simple solutions that is impssible until someone points out the obvious!

Basically I want to check to see if a certain worksheet has an autofilter applied to it when the workbook is opened, and if there is I want to remove the autofilter (ShowAllData not remove autofilter buttons) before it does anything else. The problem I'm having is accounting for when there isn't an autofilter applied and the ShowAllData errors out.

Any Help would be greatly appreciated!

Thanks

 

[Colin Legg]

Hi,

This code goes in the ThisWorkbook Class Module:

Private Sub Workbook_Open()
    
    With Sheet1
        If .AutoFilterMode Then .ShowAllData
    End With
    
End Sub

Change the sheet's codename to suit.

Colin

 

[Peter.Stevens2]

Hi Colin

Thanks for your quick reply, I thought it would be something like that! I'm still getting an error when I try to open the workbook when AutoFilterMode is on but there are no filters applied to any of the fields. I think the next issue is that there could be a filter on any one of about 40 fields when the workbook is closed.

Kind Regards

Pete

 

[Colin Legg]

Hi Pete,

My apologies... does this work?

Private Sub Workbook_Open()
    
    With Sheet1
        If .FilterMode Then .ShowAllData
    End With
    
End Sub

 

[Peter.Stevens2]

Thanks very much Colin that worked a treat!