CODEWITHSUNDEEP
vbahiddenprotectedlocked
starkiwi26
starkiwi26

Reputation: 1

VBA stops working once the sheet is protected

I have a table with filter. I wrote simple VBA so that when the user pick different option, the VBA will pick the filter and sort the table automatically. It works perfectly. I then hide, lock and protect the sheet to keep it simple and prevent the user from changing the formulas. But, once the sheet is protected (I ticked and gave permissions for all actions), still the VBA fails to run. If I switched back to "unprotected", everything works again. I am stuck, hope fellow experts can advise please. Many thanks!

Simple VBA as below:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Range("BF1").Value = "Highest $" Then
        Range("A5:CK288").Sort Key1:=Range("BG5:BG288"), Order1:=xlDescending
   End If
   If Range("BF1").Value = "Nearest end" Then
        Range("A5:CK288").Sort Key1:=Range("BC5:BC288"), Order1:=xlAscending
   End If
   If Range("BF1").Value = "Customer" Then
        Range("A5:CK288").Sort Key1:=Range("BE5:BE288"), Order1:=xlDescending
   End If
   If Range("BF1").Value = "Country" Then
        Range("A5:CK288").Sort Key1:=Range("BD5:BD288"), Order1:=xlDescending
   End If
   If Target.Address = Range("BF2").Address Then
        If Range("BF2") = "All" Then
            Range("A5").AutoFilter Field:=56
        Else
            Range("A5").AutoFilter Field:=56, Criteria1:=Range("BF2").Value
        End If
   End If
   If Target.Address = Range("BF3").Address Then
        If Range("BF3") = "All" Then
            Range("A5").AutoFilter Field:=54
        Else
            Range("A3").AutoFilter Field:=54, Criteria1:=Range("BF3").Value
        End If
   End If
End Sub

Upvotes: 0

Views: 63

Answers (1)

Andrew
Andrew

Reputation: 219

When you protect the sheet, there are options to allow the filter. Tick the Autofilter checkbox. You can also set Protection for individual cells. My English translations might be off, but right click the cell(s), select Format Cells(?), then then on the Protection tab, untick "Locked". Refer to this link.

Upvotes: 0

Related Questions