Combining 2 macros into 1

stressler

Board Regular
Joined
Jun 25, 2014
Messages
95
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?
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

stressler

Board Regular
Joined
Jun 25, 2014
Messages
95
Thanks for the response oldbrewer, but after the line "Application.EnableEvents = True" from my 1st macro, what exactly do I type? I need the wording. I've tried using the exact words, call macro2, which didn't work, and call macro Private Sub Worksheet_Change(ByVal Target As Range), which didn't work. I'm not sure of the exact wording to connect the two macros.
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,004
call macro 2 is a guide just insert name of second macro (my macros are macro 1 macro 2)

you seem to be defining things in the name of your macro
 

stressler

Board Regular
Joined
Jun 25, 2014
Messages
95
Okay, I get what you mean and was able to get my macros to run together. My problem now seems to be that when I rename my second macro to Private Sub Macro2(), instead of Private Sub Worksheet_Change(ByVal Target As Range), I'm getting a run-time error '424': Object Required. It's like it's running that macro and erroring out when I don't have anything in that field now. Something isn't working correctly. Here's my combined macro...
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
Call Macro2

End Sub
Private Sub Macro2()
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,634
Messages
5,626,011
Members
416,153
Latest member
Alvonwin

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