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?

 

[Joe4]

And be sure to post your current code. Then I can try to re-create your scenario.

There are instructions on how you can post images found in the "Posting Aids" section of this link here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html. There is also a "Test Here" forum on this site that you can use to test those tools before using them in your threads.

 

[Joe4]

Nope. Cannot open any of the links. Blocked by my company's security policy.

 

[EBoudreau]

And be sure to post your current code. Then I can try to re-create your scenario.

There are instructions on how you can post images found in the "Posting Aids" section of this link here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html. There is also a "Test Here" forum on this site that you can use to test those tools before using them in your threads.

Here's all the code I currently have.
Just to recap: I only want comments with timestamps to be enable in sheets 1 and 2.

Sheet 1:

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

Sheet 2:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim xHour As String
    Dim xMinute As String
    Dim xWord As String
    Dim rngMonitored As Range, rng As Range
    
    Set rngMonitored = Intersect(Target, Range("B4:C103,E4:F103,L4:M103,O4:P103,V4:W103,Y4:Z103,AF4:AG103,AI4:AJ103,B108:C207,E108:F1207,L108:M1207,O108:P207,V108:W207,Y108:Z207,AF108:AG207,AI108:AJ207,B212:C311,E212:F311,L212:M311,O212:P311,V212:W311,Y212:Z311,AF212:AG311,AI212:AJ311"))
    If rngMonitored Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    For Each rng In rngMonitored
        xWord = Format(rng.Value, "0000")
        xHour = Left(xWord, 2)
        xMinute = Right(xWord, 2)
        On Error Resume Next
        rng.Value = TimeValue(xHour & ":" & xMinute)
    Next rng
    
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowFormattingColumns:=Fasle, AllowFormattingRows:=True, Password:="WellingtonFrac"
    Application.EnableEvents = True
    


End Sub

Module

Sub Capitalise(rngToConvert As Range)


    Dim rng As Range
    
    For Each rng In rngToConvert
        With rng
            If Not .HasFormula Then .Value = UCase(.Value)
        End With
    Next rng


End Sub
Sub ConvertToTime(rngToConvert As Range)


    Dim rng As Range
    
    For Each rng In rngToConvert
        rng.Value = Format(rng.Value, "00\:00")
    Next rng
    
    rngToConvert.NumberFormat = "HH:MM"


End Sub

 

[Joe4]

OK. Now can you walk me through a specific example of what you do and what should happen?
I want to make sure that I update the same cells and do the same keystrokes as you.

 

[EBoudreau]

Ok so i took the code that is currently in sheet 1 and changed it to:

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

 [COLOR=#333333]End sub
[/COLOR]

Then I deleted:

If .Address <> "$A$1" Then Exit Sub

I did not get any RunTime errors in doing so, but time stamps are not auto populating in the comments i enter into any cells. Should I have done something different?

 

[Joe4]

You never answered my last post.

 

[EBoudreau]

I thought i did answer that by describing the code change.
I was expecting comments to have time stamps but they ended up not having that.

 

[Joe4]

I thought i did answer that by describing the code change.
I was expecting comments to have time stamps but they ended up not having that.

OK. Now can you walk me through a specific example of what you do and what should happen?
I want to make sure that I update the same cells and do the same keystrokes as you.

No, I am looking for an actual example of one that isn't working for you. Let me know exactly what sheet and cell you are in, and what you are entering in.

I tested it myself, and it seems to work fine for me.
I entered "a" in cell A3 on Sheet1 and it created the following comment:
"04/28/16 at 3:44 PM
a"
(so that it what I am looking for from you, an actual example that does not work, with all the details of precisely what you are entering and where).

 

[EBoudreau]

No, I am looking for an actual example of one that isn't working for you. Let me know exactly what sheet and cell you are in, and what you are entering in.

I tested it myself, and it seems to work fine for me.
I entered "a" in cell A3 on Sheet1 and it created the following comment:
"04/28/16 at 3:44 PM
a"
(so that it what I am looking for from you, an actual example that does not work, with all the details of precisely what you are entering and where).

Ok so I just entered A in cell J25 and it did indeed enter a time stamp into a comment, however that's not what I want it to do.
I cleared cell J25 of comment and the letter A and right clicked in the cell to insert a comment.. That is when I want a time stamp to occur and it is not occurring.

What is the work around for that?

It looks like I severely misunderstood the code and had a communication error in relaying my expectations of what i wanted to happen.
Thank you for clearing that up for me.

 

[Joe4]

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)?