Check autofilter before Save I have a share workbook with filter, and I want to

Question

Check autofilter before Save

I have a share workbook with filter, and I want to tell user to clear all custom filter ( before save. So if the active sheet have autofilter then message will prompt the ask user question, if the click Yes then file save and let them know the file is saved. If they click No then file will not Save and the message will tell them file is not Save. But after I couldn't make 2nd message ("The file is saved") to pop-up, what did I do wrong? Please help

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

Dim CheckResult As VbMsgBoxResult

If ActiveSheet.AutoFilterMode = True Then
  
MsgBox "Did you clear the custom filter?", vbYesNo
If CheckResult = vbYes Then
MsgBox "The file is saved"
End If
End If
ActiveSheet.AutoFilterMode = False
  
End Sub

Other option: Can I force to the AutoFilter to have the "Select All" option turn on before Save?

Thanks

Explanation / Answer

*&^%*& the following code will simply clear all of the filters but leave AutoFilter DropDowns on the ActiveSheet.

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

    With ActiveSheet
        If .AutoFilterMode = True Then
            If .FilterMode Then
                .ShowAllData
            End If
        End If
    End With
    
End Sub

The following code turns off AutoFilter and removes the dropdowns.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   
    ActiveSheet.AutoFilterMode = False
    
End Sub

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects