Worksheet Change Event When A Cell Within A Range Is Changed

[Ark68]

I have a dynamic range of cells (rng_sstart), that when one of the cells in that range changes, wish to run some code.

This is what isn't working for me:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng_sstart As Range
    
    If Not mbevents Then Exit Sub
    
    With ws_gui1
        Set rng_sstart = .Range("BD4:BD" & .Cells(.Rows.Count, "BD").End(xlUp).Row)
        If Not Intersect(Target, rng_sstart) Is Nothing Then 'CHANGE in staff start
            tgt_r = .Range(Target.Address).Row
            MsgBox "Staff Start - Row: " & tgt_r & Chr(13) & "   - Check for valid time value." & Chr(13) & "   - Check against end time."
            mbevents = True
            Stop
        End If
    End With

This code will not change if I change a value within the range defined by rng_sstart.

Thoughts

 

[Peter_SSs]

I can see a few things that might cause that to not work.

1. That code is not in the code module for whatever sheet ws_gui1 is
2. The cells in rng_sstart are formulas
3. Your 'events' have become disabled

 

[Ark68]

Hi Peter, thank you! I had an old application.enableevents=false in my code that I hadn't removed. All good, although it did expose another problem.

 

[Peter_SSs]

You're welcome.

I assume that you have solved the "another problem" or else you will ask about that here or a new thread if required.

 

[Ark68]

Yes, thanks Peter for checking. I did figure that one out on my own

 

[Peter_SSs]