VBA - Help Making Alteration to Code

[Nordicrx8]

Morning all! Happy Monday!

So the below code is working, with a caveat. Was hoping someone could review for me!

All cells in range J9:J4000 are blank. If a user types anything into any of the cells in this range, the code will delete any content in the cell to the left of it, and place "Custom Message" two cells to the left of it. This works perfectly, and as intended.

The issue I'm running into is this: If a user deletes what they entered into a cell in J9:J4000, (or even just presses delete in any cell in this range) the code still runs and makes the changes. This is not the intent.

I only need the code to run when someone type something in J9:J4000. If they delete what they typed in, it would be nice to also clear out "Custom Message" but not required.

Thoughts on how to alter the code to accomplish? To be fair, this was code I patched together, and despite doing research, I don't fully understand this line: "If Not rng Is Nothing Then" which I assume is the crux of the issue. if someone could explain it better than what I'm finding online, I'd appreciate it!

Thanks all!

Sub Worksheet_Change(ByVal Target As Range)
   Dim rng As Range
   Set rng = Intersect(Target, Me.Range("J9:J4000"))

   If Not rng Is Nothing Then
       On Error GoTo SafeExit
       Application.EnableEvents = False
    
       Dim cell As Range
       For Each cell In rng
           cell.Offset(, -1).Value = ("")
           cell.Offset(, -2).Value = ("Custom Message")
       Next
   End If

SafeExit:
   Application.EnableEvents = True
End Sub

 

[Legacy 143009]

Hi,

This should work:

Sub Worksheet_Change(ByVal Target As Range)
   Dim rng As Range
   Set rng = Intersect(Target, Me.Range("J9:J4000"))

   If Not rng Is Nothing And Not IsEmpty(rng) Then
       On Error GoTo SafeExit
       Application.EnableEvents = False
    
       Dim cell As Range
       For Each cell In rng
           cell.Offset(, -1).Value = ("")
           cell.Offset(, -2).Value = ("Custom Message")
       Next
   End If

SafeExit:
   Application.EnableEvents = True
End Sub

 

[Akuini]

I only need the code to run when someone type something in J9:J4000. If they delete what they typed in, it would be nice to also clear out "Custom Message" but not required.

Maybe this:

Sub Worksheet_Change(ByVal Target As Range)
   Dim rng As Range
   Set rng = Intersect(Target, Me.Range("J9:J4000"))

   If Not rng Is Nothing Then
       On Error GoTo SafeExit
       Application.EnableEvents = False
    
       Dim cell As Range
       For Each cell In rng
           If cell <> "" Then
            cell.Offset(, -1).Value = ("")
            cell.Offset(, -2).Value = ("Custom Message")
           Else
            cell.Offset(, -2).Value = ""
           End If
       Next
   End If

SafeExit:
   Application.EnableEvents = True
End Sub

 

[Joe4]

Note:

I am pretty sure that "Me" is totally unnecessary here:

   Set rng = Intersect(Target, Me.Range("J9:J4000"))

By default, "Worksheet_Change" event procedure will look at the sheet they are stored in if sheet is not qualified.

 

[Nordicrx8]

Maybe this:

Sub Worksheet_Change(ByVal Target As Range)
   Dim rng As Range
   Set rng = Intersect(Target, Me.Range("J9:J4000"))

   If Not rng Is Nothing Then
       On Error GoTo SafeExit
       Application.EnableEvents = False
   
       Dim cell As Range
       For Each cell In rng
           If cell <> "" Then
            cell.Offset(, -1).Value = ("")
            cell.Offset(, -2).Value = ("Custom Message")
           Else
            cell.Offset(, -2).Value = ""
           End If
       Next
   End If

SafeExit:
   Application.EnableEvents = True
End Sub

this worked perfectly - thank you!!

 

[Nordicrx8]

Hi,

This should work:

Sub Worksheet_Change(ByVal Target As Range)
   Dim rng As Range
   Set rng = Intersect(Target, Me.Range("J9:J4000"))

   If Not rng Is Nothing And Not IsEmpty(rng) Then
       On Error GoTo SafeExit
       Application.EnableEvents = False
   
       Dim cell As Range
       For Each cell In rng
           cell.Offset(, -1).Value = ("")
           cell.Offset(, -2).Value = ("Custom Message")
       Next
   End If

SafeExit:
   Application.EnableEvents = True
End Sub

This worked! And so simple too - I was totally overcomplicating things. Thank you!

 

[Nordicrx8]

Note:

I am pretty sure that "Me" is totally unnecessary here:

   Set rng = Intersect(Target, Me.Range("J9:J4000"))

By default, "Worksheet_Change" event procedure will look at the sheet they are stored in if sheet is not qualified.

Oh good to know - thank you!