Worksheet Change not working...

[wpryan]

Hi All,

I have some code that, I expect, should cause some worksheets to be visible or hidden. The code works fine as a Macro, but I want it to be in the worksheet_change event. Somehow it's not firing. The code is like this:

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim sht As Worksheet
    Dim LastRow As Long
    
    Set sht = Sheets("Data")


    LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        
    'Lasik
    If Application.WorksheetFunction.CountIf(Range("D19:D" & LastRow), "Lasik") > 0 Then
        Sheets("Lasik").Visible = True
    Else: Sheets("Lasik").Visible = False
    End If
    
    'Rings
    If Application.WorksheetFunction.CountIf(Range("D19:D" & LastRow), "ICR") > 0 Then
        Sheets("Rings").Visible = True
    Else: Sheets("Rings").Visible = False
    End If

end sub

...any ideas?

 

[Fluff]

Which module do you have that code in?

 

[wpryan]

Thanks for the response. It's in the Worksheet_Change event in the Data worksheet. Like I said, if I run the code as a stand-alone Macro, the code is working...

 

[Fluff]

If you run this

Sub chk()
Application.EnableEvents = True
End Sub

and then change any cell on the "Data" sheet, does your code trigger?

 

[wpryan]

Duh to me! That was it, I disabled events in another place and completely forgot! Thanks a lot!

 

[Fluff]

You're welcome & thanks for the feedback.

We've all been there at some point.