Why does VBA seem to have a memory?

[XrayLemi]

I have this code in a workbook.

Dim cr As Range
     Set cl = Range("H6:H5000")
     Set cl = Intersect(Target, cl)
     For Each cl In Target
     Select Case True
     Case 8 = cl.Column 'H
     If cl.Value <> "" Then
        Check = MsgBox("Is this entry correct?" & vbCrLf & "This cell cannot be edited after entering a value.", vbYesNo + vbQuestion, "Cell Lock Notification")
            If Check = vbYes Then
            Target.Rows.EntireRow.Locked = True
            Else
            Cells(cr.Row, "H").Value = ""
            End If
          End If
      Case Else
    End Select
   Next cl
  Application.EnableEvents = True
End Sub

Everything works as it is coded. However, If I change the range to ("L6:L5000") and anything associated with the range, the message box STILL appears when you enter a value in column H. It should appear after an entry in column L. I have even copied the code to word, changed the code in Word, erased the old code, made a copy of the sheet, re-pasted the new code, and it still refers back to the old range!
Why? What am I missing?
Thanks in advance,
Jim

 

[Fluff]

Ok, put a stop line in like

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, c As Range
  Stop
  Set r = Range("G6:G5000")
  Set r = Intersect(Target, r)

Then change something in col G6:G5000, the code window should open up & then you can use F8 to step through the code & see what's happening.

 

[XrayLemi]

Hi Fluff,
Okay, I have been doing allot of comparing the codes we have shared in this post. I have learned some valuable lessons. Like when you make a change in the column reference, H to L, you also need to change the case. in my code, case 8 needs to change to case 12 (to match the column). This is as I found out why things went awry. It turns out you have to make ALL the changes when you change a cell or column reference or things don't work.

This is the code I put together that seems to work perfectly.

Private Sub CommandButton1_Click()
    UpdateDataFromMasterFile
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, c As Range
  Set r = Range("G6:G5000")
  Set r = Intersect(Target, r)
  If Not r Is Nothing Then
  Application.EnableEvents = False
  For Each c In r
    Select Case True
      Case 7 = c.Column 'G
        If c.Value = "" Then
          Cells(c.Row, "H").Value = ""
          Cells(c.Row, "H").Locked = True
          Else
          Cells(c.Row, "H").Locked = False
        End If
      Case Else
    End Select
  Next c
  End If
   If Target.Cells.Count > 4 Then Exit Sub
  If Not Intersect(Target, Range("B6:B5000")) Is Nothing Then
    With Target(1, 4)
     .Value = Date
     .EntireColumn.AutoFit
    End With
    End If
     Dim p As Range
     Set p = Range("L6:L5000")
     Set p = Intersect(Target, p)
     For Each p In Target
     Select Case True
     Case 12 = p.Column 'L
     If p.Value <> "" Then
        Check = MsgBox("Is this entry correct?" & vbCrLf & "This cell cannot be edited after entering a value.", vbYesNo + vbQuestion, "Cell Lock Notification")
            If Check = vbYes Then
            Target.Rows.EntireRow.Locked = True
            Else
            Cells(p.Row, "L").Value = ""
            End If
          End If
      Case Else
    End Select
   Next p
  Application.EnableEvents = True
End Sub

I tried the code as you had said to do making the changes you said, then running it like in post #13 it didn't work.
the attached image is the differences in the two codes. I don't have a clue why one works and the other doesn't. I used arrows to show the differences in the two codes. I have a working project now but I don't know why. I hope you can shed some light on this for me.
As always THANK YOU so much.
Jim Lemieux

 

[Fluff]

Glad you got it sorted & thanks for the feedback.

 

[XrayLemi]

I was hoping if you had the time you could explain why one works and the other doesn't. I'm trying to learn as much as I can without feeling like a freeloader.