How do I merge two subs with Private Sub Worksheet_Change on sheet, which have different triggers
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have 2 private sub worksheet_change subs that need to be on the same sheet and can't work out how to combine them. Very new to this whole vba game and both subs I have found on various websites. Any help would be greatly appreciated. The 2 pieces of code are below. One gets the time a cell changes and the other dispalys a message box when another cell changes (Have updated since first post. Both are worksheet_change subs
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
And second one is
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Range("AH4"), Target) Is Nothing) Then
MsgBox "Broadcast Now!"
End If
End Sub
excel vba
asked Nov 22 '18 at 2:27
PetePete
12
add a comment |
0
I have 2 private sub worksheet_change subs that need to be on the same sheet and can't work out how to combine them. Very new to this whole vba game and both subs I have found on various websites. Any help would be greatly appreciated. The 2 pieces of code are below. One gets the time a cell changes and the other dispalys a message box when another cell changes (Have updated since first post. Both are worksheet_change subs
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
And second one is
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Range("AH4"), Target) Is Nothing) Then
MsgBox "Broadcast Now!"
End If
End Sub
excel vba
asked Nov 22 '18 at 2:27
PetePete
12
I am not sure to follow, there is no apparent reason to why you would want to merge these procedures as they are triggered by different events. If it is just a mater of which sheet they need to work with, just paste these codes in the sheet code you want. You'll have to give more details about your overall objective if you want more help.
– Display name
Nov 22 '18 at 2:39
Sorry, had changed the second one and not noticed should be the following as the one I originally posted does not work. Assuming it is because it is looking at the result of a formula, will work if I manually change it
– Pete
Nov 22 '18 at 4:08
Private Sub Worksheet_Change(ByVal Target As Range) If Not (Application.Intersect(Range("AH4"), Target) Is Nothing) Then MsgBox "Broadcast Now!" End If End Sub
– Pete
Nov 22 '18 at 4:09
add a comment |
0
0
0
I have 2 private sub worksheet_change subs that need to be on the same sheet and can't work out how to combine them. Very new to this whole vba game and both subs I have found on various websites. Any help would be greatly appreciated. The 2 pieces of code are below. One gets the time a cell changes and the other dispalys a message box when another cell changes (Have updated since first post. Both are worksheet_change subs
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
And second one is
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Range("AH4"), Target) Is Nothing) Then
MsgBox "Broadcast Now!"
End If
End Sub
excel vba
asked Nov 22 '18 at 2:27
PetePete
12
I have 2 private sub worksheet_change subs that need to be on the same sheet and can't work out how to combine them. Very new to this whole vba game and both subs I have found on various websites. Any help would be greatly appreciated. The 2 pieces of code are below. One gets the time a cell changes and the other dispalys a message box when another cell changes (Have updated since first post. Both are worksheet_change subs
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
And second one is
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Range("AH4"), Target) Is Nothing) Then
MsgBox "Broadcast Now!"
End If
End Sub
excel vba
excel vba
asked Nov 22 '18 at 2:27
PetePete
12
asked Nov 22 '18 at 2:27
PetePete
12
edited Nov 22 '18 at 4:32
Pete
asked Nov 22 '18 at 2:27
PetePete
12
asked Nov 22 '18 at 2:27
PetePete
12
asked Nov 22 '18 at 2:27
PetePete
12
12
I am not sure to follow, there is no apparent reason to why you would want to merge these procedures as they are triggered by different events. If it is just a mater of which sheet they need to work with, just paste these codes in the sheet code you want. You'll have to give more details about your overall objective if you want more help.
– Display name
Nov 22 '18 at 2:39
Sorry, had changed the second one and not noticed should be the following as the one I originally posted does not work. Assuming it is because it is looking at the result of a formula, will work if I manually change it
– Pete
Nov 22 '18 at 4:08
Private Sub Worksheet_Change(ByVal Target As Range) If Not (Application.Intersect(Range("AH4"), Target) Is Nothing) Then MsgBox "Broadcast Now!" End If End Sub
– Pete
Nov 22 '18 at 4:09
add a comment |
I am not sure to follow, there is no apparent reason to why you would want to merge these procedures as they are triggered by different events. If it is just a mater of which sheet they need to work with, just paste these codes in the sheet code you want. You'll have to give more details about your overall objective if you want more help.
– Display name
Nov 22 '18 at 2:39
Sorry, had changed the second one and not noticed should be the following as the one I originally posted does not work. Assuming it is because it is looking at the result of a formula, will work if I manually change it
– Pete
Nov 22 '18 at 4:08
Private Sub Worksheet_Change(ByVal Target As Range) If Not (Application.Intersect(Range("AH4"), Target) Is Nothing) Then MsgBox "Broadcast Now!" End If End Sub
– Pete
Nov 22 '18 at 4:09
I am not sure to follow, there is no apparent reason to why you would want to merge these procedures as they are triggered by different events. If it is just a mater of which sheet they need to work with, just paste these codes in the sheet code you want. You'll have to give more details about your overall objective if you want more help.
– Display name
Nov 22 '18 at 2:39
I am not sure to follow, there is no apparent reason to why you would want to merge these procedures as they are triggered by different events. If it is just a mater of which sheet they need to work with, just paste these codes in the sheet code you want. You'll have to give more details about your overall objective if you want more help.
– Display name
Nov 22 '18 at 2:39
Sorry, had changed the second one and not noticed should be the following as the one I originally posted does not work. Assuming it is because it is looking at the result of a formula, will work if I manually change it
– Pete
Nov 22 '18 at 4:08
Sorry, had changed the second one and not noticed should be the following as the one I originally posted does not work. Assuming it is because it is looking at the result of a formula, will work if I manually change it
– Pete
Nov 22 '18 at 4:08
Private Sub Worksheet_Change(ByVal Target As Range) If Not (Application.Intersect(Range("AH4"), Target) Is Nothing) Then MsgBox "Broadcast Now!" End If End Sub
– Pete
Nov 22 '18 at 4:09
Private Sub Worksheet_Change(ByVal Target As Range) If Not (Application.Intersect(Range("AH4"), Target) Is Nothing) Then MsgBox "Broadcast Now!" End If End Sub
– Pete
Nov 22 '18 at 4:09
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53423060%2fhow-do-i-merge-two-subs-with-private-sub-worksheet-change-on-sheet-which-have-d%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53423060%2fhow-do-i-merge-two-subs-with-private-sub-worksheet-change-on-sheet-which-have-d%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I am not sure to follow, there is no apparent reason to why you would want to merge these procedures as they are triggered by different events. If it is just a mater of which sheet they need to work with, just paste these codes in the sheet code you want. You'll have to give more details about your overall objective if you want more help.
– Display name
Nov 22 '18 at 2:39
Sorry, had changed the second one and not noticed should be the following as the one I originally posted does not work. Assuming it is because it is looking at the result of a formula, will work if I manually change it
– Pete
Nov 22 '18 at 4:08
Private Sub Worksheet_Change(ByVal Target As Range) If Not (Application.Intersect(Range("AH4"), Target) Is Nothing) Then MsgBox "Broadcast Now!" End If End Sub
– Pete
Nov 22 '18 at 4:09