Excel VBA check for Filters before Save

Question

I have a workbook that is shared. It gets into problems when multiple users are working on it and one saves with a filter on. I am trying to write a workbook code to prevent saving when there is a filter.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    If Worksheets("sheet1").AutoFilterMode = True Then
    MsgBox "Filters not allowed, remove filters ", vbExclamation, "Warning!"
    Cancel = True

   Exit Sub
   End If
   End Sub

If possible I would like to check the full workbook but will settle for each sheet. When I run this, the macro just saves. I have placed it in Microsoft Excel Object > ThisWorkbook section.

Answer 1

Your given code looks to see if a sheet named "sheet1" has filters enabled. Note that "Sheet1" (the default name for the first sheet) is not the same as "sheet1".

You could use a loop to look at each of the sheets in the workbook and test if there are filters:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wsht As Worksheet
    For Each wsht In ThisWorkbook.Worksheets
        If wsht.AutoFilterMode = True Then
            MsgBox "Filters not allowed, remove filters before saving", vbExclamation, "Warning!"
            Cancel = True
            Exit Sub
        End If
    Next wsht
End Sub 

A more user friendly approach would be to use this macro to remove any filters before saving, whilst also prompting the user that saving the workbook will remove the filters and ask if they wish to continue.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wsht As Worksheet
    For Each wsht In ThisWorkbook.Worksheets
        If wsht.AutoFilterMode = True Then
            MsgboxAnswer = MsgBox("Filters are currently active, saving this workbook will remove them. Do you want to continue?", vbYesNo)
            If MsgboxAnswer = vbYes Then RemoveFilters = True
            Exit For
        End If
    Next wsht

    If RemoveFilters Then
        For Each wsht In ThisWorkbook.Worksheets
            wsht.AutoFilterMode = False
        Next wsht
    End If
End Sub