VBA to add a line to a comment.

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
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

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, replace this...

Code:
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...

Code:
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,125
Members
414,043
Latest member
thomas Stein

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
Top