SaraWitch
Active Member
- Joined
- Sep 29, 2015
- Messages
- 370
- Office Version
- 365
- Platform
- Windows
Hello peeps,
I'm trying to find a general rule of thumb when wanting to run more than one Private Sub Worksheet_Change(ByVal Target As Range) routines. I have tried a variety of ways, but nothing is working for me.
My two codes are...
Jump to target cell when data entered:
Allow more than one option in DVL:
Any help would be appreciated![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
I'm trying to find a general rule of thumb when wanting to run more than one Private Sub Worksheet_Change(ByVal Target As Range) routines. I have tried a variety of ways, but nothing is working for me.
My two codes are...
Jump to target cell when data entered:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("X7:X10000"), Target) Is Nothing Then Exit Sub
Target.Offset(0, -8).Select
End Sub
Allow more than one option in DVL:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Value_Old As String
Dim Value_New As String
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, ActiveSheet.Range("H6:H100000")) Is Nothing Then
Application.EnableEvents = False
Value_New = Target.Value
On Error Resume Next
Application.Undo
On Error GoTo 0
Value_Old = Target.Value
If InStr(Value_Old, Value_New) Then
If InStr(Value_Old, ",") Then
If InStr(Value_Old, ", " & Value_New) Then
Target.Value = Replace(Value_Old, ", " & Value_New, "")
Else
Target.Value = Replace(Value_Old, Value_New & ", ", "")
End If
Else
Target.Value = ""
End If
Else
If Value_Old = "" Then
Target.Value = Value_New
Else
If Value_New = "" Then
Target.Value = ""
Else
If InStr(Target.Value, Value_New) = 0 Then
Target.Value = Value_Old & ", " & Value_New
End If
End If
End If
End If
Application.EnableEvents = True
Else
Exit Sub
End If
End Sub
Any help would be appreciated