How do I put 2 codes in VBA Worksheet_change

%3CLINGO-SUB%20id%3D%22lingo-sub-1181873%22%20slang%3D%22en-US%22%3EHow%20do%20I%20put%202%20codes%20in%20VBA%20Worksheet_change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1181873%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20newbie%20to%20VBA.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20production%20sheet%20with%20two%20VBA%20codes%20working%20perfectly%20fine%20seperatly.%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20code%20is%20populating%20automatic%20dates%20in%20column%203%20(C)%20and%20in%20column%2016%20(P)%20based%20on%20inputs%20in%20Column%201%2C%2012%20and%2015.%20How%20do%20I%20put%20below%20codes%20together%20in%20one%20worksheet.%20When%20I%20just%20add%20both%20it%20doesnt%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHere%20is%20the%20first%20code%20and%20its%20working%20perfectly%20fine%20if%20its%20added%20alone%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EIf%20Target.Column%20%3D%201%20Then%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3ECells(Target.Row%2C%204).Value%20%3D%20Date%20%2B%20Time%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EElse%3CBR%20%2F%3EIf%20Target.Column%20%3D%2012%20Then%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3ECells(Target.Row%2C%2016).Value%20%3D%20Date%20%2B%20Time%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EElse%3CBR%20%2F%3EIf%20Target.Column%20%3D%2015%20Then%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3ECells(Target.Row%2C%2016).Value%20%3D%20Date%20%2B%20Time%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EThen%20I%20have%20my%20second%20code%20which%20I%20also%20want%20to%20be%20in%20the%20same%20worksheet%20.%20This%20Code%20provides%20me%20a%20pop%20up%20message%20based%20on%20column%206.%20Code%20is%20also%20working%20perfectly%20alone.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EConst%20iDefaultDays%20As%20Integer%20%3D%2084%3CBR%20%2F%3EDim%20dtExpected%20As%20Date%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20Error%20GoTo%20Terminate%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Target.Cells.Count%20%26gt%3B%206%20Or%20Target.Column%20%26lt%3B%26gt%3B%206%20Then%20GoTo%20Terminate%3CBR%20%2F%3EIf%20Target.Value%20%3D%20%22%22%20Then%3CBR%20%2F%3ETarget.Offset(0%2C%202).ClearContents%3CBR%20%2F%3EGoTo%20Terminate%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20IsDate(Target.Value)%20Then%3CBR%20%2F%3EMsgBox%20%22Invalid%20value%20-%20please%20enter%20a%20date%22%2C%20vbExclamation%20%2B%20vbOKOnly%3CBR%20%2F%3ETarget.ClearContents%3CBR%20%2F%3ETarget.Select%3CBR%20%2F%3EElse%3CBR%20%2F%3EdtExpected%20%3D%20Target.Value%20%2B%20iDefaultDays%3CBR%20%2F%3EIf%20MsgBox(%22Expected%20delivery%20date%3A%20%22%20%26amp%3B%20dtExpected%20%26amp%3B%20String(2%2C%20vbCr)%20%26amp%3B%20%22Accept%20this%20date%3F%22%2C%20vbYesNo%20%2B%20vbQuestion)%20%3D%20vbNo%20Then%3CBR%20%2F%3EdtExpected%20%3D%20InputBox(%22Manually%20enter%20expected%20delivery%20date%22%2C%20%2C%20dtExpected)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ETarget.Offset(0%2C%202).Value%20%3D%20dtExpected%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ETerminate%3A%3CBR%20%2F%3EIf%20Err%20Then%3CBR%20%2F%3EDebug.Print%20%22Error%22%2C%20Err.Number%2C%20Err.Description%3CBR%20%2F%3EErr.Clear%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%20alot!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1181873%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Regular Visitor

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! 

 

 

 

0 Replies