Adding new code to existing

E

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
153
I have code already in my worksheet.

What is the correct way to add more functions to call?

For example:
I have this code already:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)



    Dim rng As Range
    
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="WellingtonFrac"
    
    Set rng = Intersect(Target, Range("E8:CZ31, E34:CZ45"))
    If Not rng Is Nothing Then Call Capitalise(rng)
        
    Set rng = Intersect(Target, Range("D53:D1583"))
    If Not rng Is Nothing Then Call ConvertToTime(rng)
    
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingColumns:=True, AllowFormattingRows:=False, Password:="WellingtonFrac"
    Application.EnableEvents = True


End Sub

And I would just like to add this code:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address <> "$A$1" Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Dim strNewText$, strCommentOld$, strCommentNew$
strNewText = .Text
If Not .Comment Is Nothing Then
strCommentOld = .Comment.Text & Chr(10) & Chr(10)
Else
strCommentOld = ""
End If
On Error Resume Next
.Comment.Delete
Err.Clear
.AddComment
.Comment.Visible = False
.Comment.Text Text:=strCommentOld & _
Format(VBA.Now, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub
Whats the proper way to do so?
 
Last edited:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
You can only have one Worksheet_Change procedure per sheet.
So as long as you don't have any code in the first block that exits the procedure before the End Sub statement, you should be able to drop the block of code from the second inside the other one (under the original block of code).
 
Upvote 0
If you want to call the new code I would do as follows:

Make it a simple subroutine either stored in a module or the worksheet. I have called this new_code. Then call this from the event and pass the target as the argument

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rng As Range
    
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="WellingtonFrac"
    
    Set rng = Intersect(Target, Range("E8:CZ31, E34:CZ45"))
    If Not rng Is Nothing Then Call Capitalise(rng)
        
    Set rng = Intersect(Target, Range("D53:D1583"))
    If Not rng Is Nothing Then Call ConvertToTime(rng)
    
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingColumns:=True, AllowFormattingRows:=False, Password:="WellingtonFrac"
    Application.EnableEvents = True


    call new_code(target)
End Sub


Sub new_code(target as range)


With Target
If .Address <> "$A$1" Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Dim strNewText$, strCommentOld$, strCommentNew$
strNewText = .Text
If Not .Comment Is Nothing Then
strCommentOld = .Comment.Text & Chr(10) & Chr(10)
Else
strCommentOld = ""
End If
On Error Resume Next
.Comment.Delete
Err.Clear
.AddComment
.Comment.Visible = False
.Comment.Text Text:=strCommentOld & _
Format(VBA.Now, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With


End sub
 
Last edited:
Upvote 0
Andy2016 said:
If you want to call the new code I would do as follows:

Make it a simple subroutine either stored in a module or the worksheet. I have called this new_code. Then call this from the event and pass the target as the argument

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rng As Range
    
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="WellingtonFrac"
    
    Set rng = Intersect(Target, Range("E8:CZ31, E34:CZ45"))
    If Not rng Is Nothing Then Call Capitalise(rng)
        
    Set rng = Intersect(Target, Range("D53:D1583"))
    If Not rng Is Nothing Then Call ConvertToTime(rng)
    
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingColumns:=True, AllowFormattingRows:=False, Password:="WellingtonFrac"
    Application.EnableEvents = True


    call new_code(target)
End Sub


Sub new_code(target as range)


With Target
If .Address <> "$A$1" Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Dim strNewText$, strCommentOld$, strCommentNew$
strNewText = .Text
If Not .Comment Is Nothing Then
strCommentOld = .Comment.Text & Chr(10) & Chr(10)
Else
strCommentOld = ""
End If
On Error Resume Next
.Comment.Delete
Err.Clear
.AddComment
.Comment.Visible = False
.Comment.Text Text:=strCommentOld & _
Format(VBA.Now, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With


End sub
Click to expand...

Ok. I see what you're saying. Now I tried putting that in place, and from what I understood was this this code that we added was supposed to automatically put the date and time into any comments that are entered into any cells.That is not working which is what I want it to do.

I would like to be able to have the date and time fill into any comment entered into any cell across the entire workbook.
I've been looking all over to find something like that but nothing has worked for me.
Any suggestions?
 
Upvote 0
Joe4 said:
You can only have one Worksheet_Change procedure per sheet.
So as long as you don't have any code in the first block that exits the procedure before the End Sub statement, you should be able to drop the block of code from the second inside the other one (under the original block of code).
Click to expand...

Thank you for that insight. That makes sense.
 
Upvote 0
Ok. I see what you're saying. Now I tried putting that in place, and from what I understood was this this code that we added was supposed to automatically put the date and time into any comments that are entered into any cells.That is not working which is what I want it to do.
Click to expand...
This line of code here:
Code: 
If .Address <> "$A$1" Then Exit Sub
makes the code ONLY run on cell A1. If you want the Date/Time stamp in the comment on ANY cell, you need to remove that line.

I would like to be able to have the date and time fill into any comment entered into any cell across the entire workbook.
I've been looking all over to find something like that but nothing has worked for me.
Any suggestions?
Click to expand...
Worksheet_Change event procedures are found in specific Sheet modules only. If you want it to apply to ALL sheets, you need to add to every Sheet module.

However, if you put your "New_Code" procedure in a Standard module as a Public procedure, it can be called from all the sheet modules. So that part of the code would not need to be repeated.
 
Upvote 0
My "newbie" status at code is tripping me up.

I inserted a module and pasted the code without the ...
Code: 
If .Address <> "$A$1" Then Exit Sub

...and i still wasn't able to get time stamps in new comments. Would it be easier if I post a link to the spreadsheet so it can be examined a little closer?
 
Last edited:
Upvote 0
EBoudreau said:
My "newbie" status at code is tripping me up.

I inserted a module and pasted the code without the ...
Code: 
If .Address <> "$A$1" Then Exit Sub

...and i still wasn't able to get time stamps in new comments. Would it be easier if I post a link to the spreadsheet so it can be examined a little closer?
Click to expand...

Yes a spreadsheet example is always easier
 
Upvote 0
Would it be easier if I post a link to the spreadsheet so it can be examined a little closer?
Click to expand...
Not for me, since I cannot download files off of the internet from my current location (security policy).

Which sheet are you trying to update?
Which sheet module is your Worksheet_Change event in?

Are you sure that you events are enabled?
You always need to be careful when using/testing code that has lines like:
Code: 
Application.EnableEvents = False
in it.
This is code that disables event procedures from running. Usually, they are put in place while changes are being made by your code, so the event procedure does not call itself and get stuck in an endless loop. Usually, after the change is made by the code, there is another like to turn them back on that looks like this:
Code: 
Application.EnableEvents = True
However, in your testing, if you get to the line that disables events, but never gets to the line to re-enable them, your code will not work until you turn the events back on. You can do that by running a simple one line macro to turn them back on:
Code: 
Sub TurnEventsOn()
    Application.EnableEvents = True
End Sub
 
Upvote 0
There are Worksheet Change events in both Sheets 1 and 2.

They do both have the
Code: 
Application.EnableEvents = False
and
Code: 
Application.EnableEvents = True
in them.

There is a module as well at the very bottom which has ConvertToTime and Capitalise in it.

I would like the Comments enable in both sheet 1 and sheet 2.

Give me a moment and I can upload screenshots of what i'm working with.
........
Here we go. if you can open the link and at least view, maybe that would help...

Sheet 1
https://drive.google.com/open?id=0B0oQEry29JNjcGZVUDhNckRWdzg
Sheet 2
https://drive.google.com/open?id=0B0oQEry29JNjbl9VLUdMakpXSTA
Module
https://drive.google.com/open?id=0B0oQEry29JNjZ3A3Zks3czdSU2s
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
VBA - Intersect code being re-initiated
Replies
1
Views
137
Peter_SSs
Peter_SSs
E
  • Solved
Quick VBA code to Hide Rows based on Cell Value
Replies
3
Views
171
Joe4
Joe4
M
  • Question
Sheet code runtime error 1004
Replies
3
Views
121
MeaclH
M
J
  • Solved
help with current date VBA code
Replies
4
Views
202
Alex Blakenburg
A
J
  • Solved
VBA to search or autofilter
Replies
2
Views
295
Jagat Pavasia
J

Forum statistics

Threads
1,221,418
Messages
6,159,791
Members
451,589
Latest member
Harold14

Share this page

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
Back
Top