VBA - Help Making Alteration to Code

Nordicrx8

Board Regular
Joined
Dec 10, 2015
Messages
144
Office Version
  1. 365
Platform
  1. Windows
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!

VBA Code:
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi,

This should work:
VBA Code:
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
 
Upvote 0
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:
VBA Code:
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
 
Upvote 1
Note:

I am pretty sure that "Me" is totally unnecessary here:
VBA Code:
   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.
 
Upvote 0
Maybe this:
VBA Code:
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!! 💛
 
Upvote 0
Hi,

This should work:
VBA Code:
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!
 
Upvote 0
Note:

I am pretty sure that "Me" is totally unnecessary here:
VBA Code:
   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!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top