VBA to add a line to a comment.

[dbmathis]

Hi all,

Consider that I have two workbooks: book1 aand book2.

Using VBA within book1, how can I add a comment to book2 sheet1 cell D7:

Example comment --> 12/1 $2.00 Blah

Then using the same VBA if a comment already exists in cell D7 then the VBA finds the next available blank line in the comment and adds data the blank line below what was already in the comment.

Maybe this is not even possible, but I am sure you all know.

Thanks.

 

[parry]

Hi, replace this...

If CheckBox1 = True Then
            On Error Resume Next
                Tmp = Range("I75").Comment.Text
                If Err.Number = 0 Then
                    Range("I75").Comment.Text Text:=ActiveCell.Comment.Text & _
                Chr(10) & "TextBox1.Text"
                Else
                    Range("I75").AddComment
                    Range("I75").Comment.Text Text:="TextBox1.Text"
                End If
            On Error GoTo 0
        End If

With this...

If CheckBox1 = True Then
            On Error Resume Next
                Tmp = Range("I75").Comment.Text
                If Err.Number = 0 Then
                    Range("I75").Comment.Text Text:=Range("I75").Comment.Text & _
                Chr(10) & TextBox1.Text
                Else
                    Range("I75").AddComment
                    Range("I75").Comment.Text Text:=TextBox1.Text
                End If
            On Error GoTo 0
        End If

Issues you had...
1) If theres a comment in I75 your actually reading the comment from the active cell which may not necessarily be I75.
2) If you need to add a comment then your entering a literal string of "TextBox1.Text" rather than what text is contained in the textbox.

hth