Forum Discussion

Arnis1625's avatar
Arnis1625
Copper Contributor
Feb 19, 2020

How do I put 2 codes in VBA Worksheet_change

Hi, 

I am a newbie to VBA. 

I have a production sheet with two VBA codes working perfectly fine seperatly. 

One code is populating automatic dates in column 3 (C) and in column 16 (P) based on inputs in Column 1, 12 and 15. How do I put below codes together in one worksheet. When I just add both it doesnt help. 

Here is the first code and its working perfectly fine if its added alone:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Cells(Target.Row, 4).Value = Date + Time
Application.EnableEvents = True
Else
If Target.Column = 12 Then
Application.EnableEvents = False
Cells(Target.Row, 16).Value = Date + Time
Application.EnableEvents = True
Else
If Target.Column = 15 Then
Application.EnableEvents = False
Cells(Target.Row, 16).Value = Date + Time
Application.EnableEvents = True
End If
End If
End If

End sub

 

Then I have my second code which I also want to be in the same worksheet . This Code provides me a pop up message based on column 6. Code is also working perfectly alone. 

 

Private Sub Worksheet_Change(ByVal Target As Range)
Const iDefaultDays As Integer = 84
Dim dtExpected As Date

On Error GoTo Terminate

If Target.Cells.Count > 6 Or Target.Column <> 6 Then GoTo Terminate
If Target.Value = "" Then
Target.Offset(0, 2).ClearContents
GoTo Terminate
End If

Application.EnableEvents = False

If Not IsDate(Target.Value) Then
MsgBox "Invalid value - please enter a date", vbExclamation + vbOKOnly
Target.ClearContents
Target.Select
Else
dtExpected = Target.Value + iDefaultDays
If MsgBox("Expected delivery date: " & dtExpected & String(2, vbCr) & "Accept this date?", vbYesNo + vbQuestion) = vbNo Then
dtExpected = InputBox("Manually enter expected delivery date", , dtExpected)
End If
Target.Offset(0, 2).Value = dtExpected
End If

Terminate:
If Err Then
Debug.Print "Error", Err.Number, Err.Description
Err.Clear
End If
Application.EnableEvents = True
End Sub

 

Thanks  alot! 

 

 

 

No RepliesBe the first to reply

Resources