Multiple Private Sub Worksheet_Change

Oryx

New Member
Joined
Apr 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
846
Office Version
  1. 365
Platform
  1. Windows
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.

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

New Member
Joined
Apr 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
That works perfectly and thanks for the tips, I am just a beginner so very useful for me.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
Here is my simplified code but not really tested
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,601
Members
417,154
Latest member
gm_jagath

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
Top