Multiple Private Sub Worksheet_Change

[Oryx]

Hello,

I need help with combining these two codes into the Worksheet_Change sub, I can get them to work on their own but can't combine them properly.

In the first sub I am ensuring users enter a date in the date field when entering data in other columns in the row:

If Target.Cells.Count > 1 Then Exit Sub
Dim ans As String
Application.EnableEvents = False
ans = "Please enter a date for this activity."
If Target.Column = 3 And Target.Offset(, -1).Value = "" Then: MsgBox ans: Target.Value = "": Target.Offset(, -1).Select
If Target.Column = 4 And Target.Offset(, -1).Value = "" Then: MsgBox ans: Target.Value = "": Target.Offset(, -2).Select
If Target.Column = 5 And Target.Offset(, -1).Value = "" Then: MsgBox ans: Target.Value = "": Target.Offset(, -3).Select
Application.EnableEvents = True
End Sub

The second sub clears the value in column 6 if users make changes to column 5:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 5 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents
End If
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

Thanks!

 

[6StringJazzer]

Why do you even have exitHandler? You do not set error handling to GoTo this label. Also, you do not need an Exit Sub as the last line of the Sub. It is going to finish anyway.

This will combine them, and gives a tweak to the logic of the final If. However, not knowing all of the logic of your worksheet I can't say this is the best way to combine them. But it should work.

Also please note that your code will be much more readable if you use code tags, as I've done below.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Cells.Count > 1 Then Exit Sub
   
   Dim ans As String
   Application.EnableEvents = False
   
   ans = "Please enter a date for this activity."
   If Target.Column = 3 And Target.Offset(, -1).Value = "" Then: MsgBox ans: Target.Value = "": Target.Offset(, -1).Select
   If Target.Column = 4 And Target.Offset(, -1).Value = "" Then: MsgBox ans: Target.Value = "": Target.Offset(, -2).Select
   If Target.Column = 5 And Target.Offset(, -1).Value = "" Then: MsgBox ans: Target.Value = "": Target.Offset(, -3).Select
   
   On Error Resume Next
   If Target.Column = 5 And Target.Validation.Type = 3 Then
      Application.EnableEvents = False
      Target.Offset(0, 1).ClearContents
   End If
   
exitHandler:
   Application.EnableEvents = True
   
End Sub

 

[Oryx]

That works perfectly and thanks for the tips, I am just a beginner so very useful for me.

 

[Zot]

That works perfectly and thanks for the tips, I am just a beginner so very useful for me.

When there is any change in column 5, either blank or no, as long as that particular cell is data Validation List type, you want to clear next column?

 

[Zot]

Here is my simplified code but not really tested

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

Dim ans As String

On Error Resume Next
If Not Intersect(Target, Range("C:C, D:D, E:E")) Is Nothing Then
    Application.EnableEvents = False
    ans = "Please enter a date for this activity."
    If Range("B" & Target.Row).Value = "" Then: MsgBox ans: Target.Value = "": Range("B" & Target.Row).Select
    If Target.Column = 5 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).ClearContents
    End If
    Application.EnableEvents = True
End If
On Error GoTo 0

End Sub