Not Sure Why My Worksheet Change Code Isn't Working : Target Address

[Ark68]

This is code I have in my worksheet module. It is supposed recognize changes to cells D4 or D5. It doesn't appear to be working. When I enter a value in D4 and hit TAB or RETURN, nothing happens. It jumps to the line I highlighted in red. Looking for discussion as to what I did wrong.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not mbevents Then Exit Sub
    Application.ScreenUpdating = False
    Unprotect
    If Not Intersect(Target, Range("$D$4")) Is Nothing Then
        If Range("D4") = "Surname, Given" Then
            With Range("D4")
                .Value = "Surname, Given"
                .Font.Italic = True
                .Font.Color = RGB(30, 155, 162)
                .Font.Size = 8
            End With
        Else
            With Range("D4")
                uname = .Value
                .Font.Italic = False
                .Font.Color = RGB(19, 65, 98)
                .Font.Size = 11
            End With
        End If
        Range("D5").Select
    End If
        
 If Not Intersect(Target, Range("$D$5")) Is Nothing Then
 Stop
        If Range("D5") = "Select" Then
            With Range("D5")
                .Value = "Select"
                .Font.Italic = True
                .Font.Color = RGB(30, 155, 162)
                .Font.Size = 8
            End With
        Else
            With Range("D5")
                wcentre = .Value
                .Font.Italic = False
                .Font.Color = RGB(19, 65, 98)
                .Font.Size = 11
            End With
        End If
    End If
    Protect
    Call AddScreenTipTextToColumnC
    Stop
    Initiate1
    Application.ScreenUpdating = True
End Sub

 

[offthelip]

Have you tried removing this line:

    If Not mbevents Then Exit Sub

You also need to turn off events put this at the top of your code

Application.EnableEvents = False

and this at the bottom

Application.EnableEvents = True

this will prevent your code going into a loop

 

[Micron]

Stop is for stopping the code for testing, so it definitely ran. Maybe you want Exit Sub instead?
Put a break point at the start, edit the sheet to run the code and step through to see if it does what you expect. You check the values of variables/cells/ranges/whatever, as you go.
I'm more experienced in Access and AFAIK, Nothing in vba is the state of an object variable. If there is no object assigned to it, or if it has been assigned the value of Nothing, testing for Nothing will return true. I can't see how this would apply as you are using it.

I think you don't have Option Explicit at the top of the module and that you're getting away with not declaring variables.

 

[Ark68]

Thanks all for your help. Turns out that this ...

    Protect
    Call AddScreenTipTextToColumnC
    Stop
    Initiate1
    Application.ScreenUpdating = True

... was in the wrong spot.

It would have been difficult to answer not knowing the "flow", so I apologize.

This is how it should have looked ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not mbevents Then Exit Sub
    Application.ScreenUpdating = False
    Unprotect
    If Not Intersect(Target, Range("$D$4")) Is Nothing Then
        If Range("D4") = "Surname, Given" Then
            With Range("D4")
                .Value = "- Surname, Given -"
                .Font.Italic = True
                .Font.Color = RGB(30, 155, 162)
                .Font.Size = 8
            End With
        Else
            With Range("D4")
                uname = .Value
                .Font.Italic = False
                .Font.Color = RGB(19, 65, 98)
                .Font.Size = 11
            End With
        End If
        Range("D5").Select
    End If
        
 If Not Intersect(Target, Range("$D$5")) Is Nothing Then
 'Stop
        If Range("D5") = "- Select -" Then
            With Range("D5")
                .Value = "Select"
                .Font.Italic = True
                .Font.Color = RGB(30, 155, 162)
                .Font.Size = 8
            End With
        Else
            With Range("D5")
                wcentre = .Value
                .Font.Italic = False
                .Font.Color = RGB(19, 65, 98)
                .Font.Size = 11
            End With
        End If
        Protect
        Application.ScreenUpdating = True
        Call AddScreenTipTextToColumnC
        'Stop
        Initiate1
    End If

End Sub

 

[bebo021999]

May be:

Range("D4") = "Surname, Given"

should be:

Range("D4").value = "Surname, Given"

similar to D5

???

 

[Micron]

In Access, Value is the default property for most controls so there's no need to use it and most experience developers don't. From my limited playing around in Excel vba, it seems that Value is not required for entering anything into a range or cell either. Anyone, feel free to correct me if I'm wrong.