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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
this line of code inserted and formatted to your needs may work
Code:
Workbooks("Book2").Sheets("Sheet1").Cells(65536.4).End(xlUp).oFfset(1,0) = "Comment"

I had a period instead of comma in the first one in this part cells(65536.1)
 
Upvote 0
Well, "didn't work" isn't exacltly and error message I can diagnose...care to elaborate on what did not work...what error you got, and if you workbooks and sheetnames don't match it won't work

I am guessing a run time error occurred so do this, I kind of rushed it...the below is tested and run from "book1"
Code:
Workbooks("book2").Sheets("Sheet1").Cells(65536, 4).End(xlUp).Offset(1, 0) = "Comment"
 
Upvote 0
I have two workbooks in the same directory. book1 and book2. I run the code from book1.

Runtim error 9:

Subscript out of range.
 
Upvote 0
Heres an example applying the text "Your new line" to the active cell. If the active cell already has a comment this will be added to a new line.

hth

Code:
Sub Example()
Dim Tmp As String

On Error Resume Next
Tmp = ActiveCell.Comment.Text
If Err.Number = 0 Then
    ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & _
        Chr(10) & "Your new line"
Else
    ActiveCell.AddComment
    ActiveCell.Comment.Text Text:="Your new line"
End If
On Error GoTo 0

End Sub
 
Upvote 0
hmm, I didn't get that...

YOu could do

Windows("book2").Activate
Sheets("Sheet1").Cells(65536, 4).End(xlUp).Offset(1, 0) = "Comment"
Windows("Book1").Activate

which also worked on my end from Book1.xls

The above posters code works for adding the littel comment boxes that pop up in a cell as well, but I am not sure that is what you were looking for..if so, I was misunderstanding your intent.
 
Upvote 0
Parry, or anyone that can see what I am doing wrong.

I am trying to adapt parry's code to what I am trying to do:

Here is the code.

Rich (BB code):
Private Sub CommandButton1_Click()
Dim Tmp As String
    With Sheets("Transaction Register")
        If CheckBox1 = True Then
                .[M41].End(3)(2, 1).Value = TextBox5.Text
                .[K41].End(3)(2, 1).Value = TextBox3.Text
                .Range("C212").End(xlUp).Offset(2, -1).Value = TextBox2.Value
                .Range("C212").End(xlUp).Offset(2, 1).Value = TextBox3.Value
                .Range("C212").End(xlUp).Offset(2, 2).Value = TextBox5.Value
                .Range("C212").End(xlUp).Offset(3, 1).Value = TextBox6.Value
                .Range("C212").End(xlUp).Offset(2, 0).Value = TextBox1.Value
        End If
    End With
    With Sheets("Cash Flow Statement")
        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
    End With
    TextBox1.Text = ""
    TextBox2.Text = ""
    TextBox3.Text = ""
    TextBox5.Text = ""
    TextBox6.Text = ""
End Sub

I don't get any errors when I run this code. What I am trying to get this code to do (specifically the code in red) is have TextBox1, TextBox2, and TextBox3 form one line replacing "Your new line" that is in parry's above example.

The above exaple of parrys works great by itself. Now that I put it into my code (in red above) it does not do anything.

Thanks for the help.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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