VBA Worksheet_change or Worksheet_selectionchange not auto-triggered

[dellzy]

Hi Experts,

I have 2 worksheets which I coded (on each worksheet) to autofilter as per criteria whenever user select from the validation list. However, I don't know why it only works on the first worksheet. The 2nd worksheet only will autofilter when I click elsewhere and then click back the target cell. I tried to trick the macro to select other cell then select back the target cell upon selection made, but it still not working until I physically click the cells. Is there anyway to overcome this, please? Because I need to restrict the user to use only the validation list to filter the data and not meddle with the headers in the table. Been trying to solve this for 2 days already. ;(

Below is the code on the second worksheet:-

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Dim LastRow As Long


ActiveSheet.Unprotect Password:="abcd1234"


If Target.Address <> "$C$3" Then Exit Sub
If Target.Address = "$C$3" Then
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("A4:AC" & LastRow).AutoFilter Field:=1, Criteria1:=Array(Range("$C$3"), "="), Operator:=xlFilterValues
ActiveSheet.Protect Password:="abcd1234"
End If
End Sub

Appreciate the experts' help.

Thank you in advance.

 

[steve the fish]

You need Worksheet_Change rather than Worksheet_SelectionChange if you want it to fire when you alter the dropdown. Using a dropdown doesn't change the selection.

 

[dellzy]

Hi Steve,

Thanks for the reply. Actually, I have tried using the Worksheet_Change before I use Worksheet_SelectionChange. But it didn't triggered anything even I click physically or not after selection. But it's funny that it works on the 1st worksheet. Only when I changed to Worksheet_SelectionChange then it triggers the job BUT after I click elsewhere then back to clicking the target cell.

I really don't understand what's holding it from doing upon selection.