Adding new code to existing

[EBoudreau]

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:

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:

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?

 

[EBoudreau]

So, it sounds like this automated comment code is not something that you came up with (perhaps you found it somewhere online?), and it doesn't work the way that you want. That is very different than what I thought you original request was (which appeared to be how to combine the two sections of existing code).

The way that code works is that when an entry is made in a cell, it adds a timestamp and the content of that cell to a comment.
However, it sounds like you want to add a timestamp somewhere when you manually insert a comment? Is that right? Where does the timestamp go (at the end of the comment, or in some cell)?

I did want to combine, unless there is another way to just add the code so that it is called with what was originally there.
Otherwise, I did find that code online and gave it a try, but i mis-understood the description.

I am indeed looking to manually enter comments, where the date and time stamp automatically populate in the comment window right under the user name. Is that a possibility? I have to enter a lot of comments through out the day and need the date and time for reference in the future. It's getting really old to type that in every time.

Does that description make sense?

 

[Joe4]

Well, the problem is that Comments do not trigger the Worksheet_Change event to run (which is what you are seeing), only changes to the actual cell do. And unfortunately, there really isn't any event that editing Comments will trigger. I searched the internet, and the best I could find was the following: excel vba - How to trigger VBA code after adding a comment? - Stack Overflow

You might be better off trying to come up with something where you run a macro that prompts a user for Input (i.e. what cell to put the comment in and what the comment should be), and then have that macro populate the comment with your date/time stamp. You would just need to run that code instead of entering the comment manually.

 

[EBoudreau]

I see what you're saying now. That explains why I hadn't been able to find that or examples of that anywhere.

Thank you so much for the assistance you have provided. It has been a great help, in improving my file and my understanding of code.
I'm not fluent in code at all so all the help is greatly appreciated!

 

[Joe4]

You are welcome. Hopefully, you will come up with something that works for you.