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.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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)
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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"
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064

ADVERTISEMENT

I have two workbooks in the same directory. book1 and book2. I run the code from book1.

Runtim error 9:

Subscript out of range.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064

ADVERTISEMENT

parry, THANK YOU! :)
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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.
 

dbmathis

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

Watch MrExcel Video

Forum statistics

Threads
1,122,847
Messages
5,598,429
Members
414,238
Latest member
juxion

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