Good morning. I have two pre-existing macros used separately, but want to combine them into 1 macro that is used together. I'm very new to VBA code so I'm not positive what connector phrase I should be using to ensure both run (end if, end sub, etc...)
Here's the first one...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c2:Aa2")) Is Nothing Then
With Target(0, 1)
.Value = Date
End With
End If
' Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String, newVal As String
On Error GoTo exitHandler
If Target.Count > 1 Or Target.Text = "" Then Exit Sub
' If Target.SpecialCells(xlCellTypeSameValidation) _
' Is Nothing Then Exit Sub
If Intersect(Target, Range("c:aa")) _
Is Nothing Then Exit Sub
Application.EnableEvents = False
newVal = Target.Text
Application.Undo
oldVal = Target.Text
Target.Value = newVal
If oldVal = "" Then GoTo exitHandler
If oldVal = newVal Then
Target.Value = ""
ElseIf InStr(1, oldVal, newVal) > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
Else
Target.Value = Replace(oldVal, newVal & Chr(1), "")
End If
Else
Target.Value = oldVal & Chr(10) & newVal
End If
exitHandler:
Application.EnableEvents = True
End Sub
And here's my 2nd one...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, sCom As String
For Each c In Target
If c.Value = "Yes" Or c.Value = "yes" Then
If Not Intersect(c, Range("C48:AA54")) Is Nothing Then
Application.EnableEvents = False
If c.Comment Is Nothing Then
sCom = InputBox("enter your comment for cell " & c.Address & " and it will be inserted")
If sCom = "" Then
Application.EnableEvents = True
Exit Sub
End If
c.AddComment sCom
Else
sCom = InputBox("cell " & c.Address & " already has a comment. Enter your addition now.")
If sCom = "" Then
Application.EnableEvents = True
Exit Sub
End If
c.Comment.Text c.Comment.Text & sCom
End If
End If
End If
Application.EnableEvents = True
Next c
End Sub
How do I combine them into 1 large macro, instead of 2 individual macros?
Here's the first one...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c2:Aa2")) Is Nothing Then
With Target(0, 1)
.Value = Date
End With
End If
' Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String, newVal As String
On Error GoTo exitHandler
If Target.Count > 1 Or Target.Text = "" Then Exit Sub
' If Target.SpecialCells(xlCellTypeSameValidation) _
' Is Nothing Then Exit Sub
If Intersect(Target, Range("c:aa")) _
Is Nothing Then Exit Sub
Application.EnableEvents = False
newVal = Target.Text
Application.Undo
oldVal = Target.Text
Target.Value = newVal
If oldVal = "" Then GoTo exitHandler
If oldVal = newVal Then
Target.Value = ""
ElseIf InStr(1, oldVal, newVal) > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
Else
Target.Value = Replace(oldVal, newVal & Chr(1), "")
End If
Else
Target.Value = oldVal & Chr(10) & newVal
End If
exitHandler:
Application.EnableEvents = True
End Sub
And here's my 2nd one...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, sCom As String
For Each c In Target
If c.Value = "Yes" Or c.Value = "yes" Then
If Not Intersect(c, Range("C48:AA54")) Is Nothing Then
Application.EnableEvents = False
If c.Comment Is Nothing Then
sCom = InputBox("enter your comment for cell " & c.Address & " and it will be inserted")
If sCom = "" Then
Application.EnableEvents = True
Exit Sub
End If
c.AddComment sCom
Else
sCom = InputBox("cell " & c.Address & " already has a comment. Enter your addition now.")
If sCom = "" Then
Application.EnableEvents = True
Exit Sub
End If
c.Comment.Text c.Comment.Text & sCom
End If
End If
End If
Application.EnableEvents = True
Next c
End Sub
How do I combine them into 1 large macro, instead of 2 individual macros?