Thread: Link Autofilter to cell

Hi,

I linked cell A1 to autofilter in A3, A1 get data from drop down list in C1.
the problem every time when I change value in C1, I need to click A1 than click F2 to work. see the attached file.

Your help is needed.

Thanks

You don't need the A1 reference, this will trigger properly off of C1. I added a "Show All" option that will unfilter the table, too. You can also delete the C1 value so it's blank to also unfilter the table.

It's still pretty much the same macro, you just move the event macro into the Sheet2 module, then edit the code to apply the filter commands on Sheet1. Like so...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
    
    Application.EnableEvents = False
    
    If Not Intersect(Target, [A1]) Is Nothing Then
        Select Case Target.Value
            Case "Show All", ""
                Sheets("Sheet1").AutoFilterMode = False
            Case Else
                Sheets("Sheet1").Range("A1").AutoFilter 1, Target.Value
        End Select
        
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

The sheet you uploaded is working fine. Sheet2 is protected but the cell with the drop down is not protected thus you can still use the drop down, and that's all it takes to trigger the Autofilter on Sheet1 which is not protected.

Ah, I see what you mean. The simplest way to resolve running macro on protected sheets is to FIX the protection when then workbook opens so that you CAN run macro on the protected sheet. There is one parameter you can set when protecting a sheet called UserInterfaceOnly:=True that is added to a protection command so the protection no longer applies to VBA, only the the user. Macros are then free to do what they will on the protected sheet.

Simplest.... put this macro into a regular module1:

Sub ProtectData()
    Sheets("Sheet1").Protect _
        DrawingObjects:=False, _
        Contents:=True, _
        Scenarios:=False, _
        AllowFiltering:=True, _
        UserInterfaceOnly:=True
End Sub

Then put this macro into the ThisWorkbook module:

Private Sub Workbook_Open()
    ProtectData
End Sub

The flag UserInterfaceOnly does not persist when the workbook is closed, it must be reactivated each time you open the workbook. So, this will do that. Also, if you unprotect sheet1 to work on it, reprotect it using the macro ProtectData so that flag gets set properly again.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.