Page 1 of 2 12 LastLast
Results 1 to 10 of 11

VBA to add a line to a comment.

This is a discussion on VBA to add a line to a comment. within the Excel Questions forums, part of the Question Forums category; Hi all, Consider that I have two workbooks: book1 aand book2. Using VBA within book1, how can I add a ...

  1. #1
    Board Regular dbmathis's Avatar
    Join Date
    Sep 2002
    Location
    Austin, Texas USA
    Posts
    1,064

    Default VBA to add a line to a comment.

    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.
    After all this is over, all that will really have mattered is how we treated each other.

    Excel Version: 2007
    OS: Windows 7

  2. #2
    Board Regular XLGibbs's Avatar
    Join Date
    Feb 2005
    Location
    Buffalo NY
    Posts
    2,446

    Default

    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)

  3. #3
    Board Regular dbmathis's Avatar
    Join Date
    Sep 2002
    Location
    Austin, Texas USA
    Posts
    1,064

    Default

    Doesn't seem to work if I run it.
    After all this is over, all that will really have mattered is how we treated each other.

    Excel Version: 2007
    OS: Windows 7

  4. #4
    Board Regular XLGibbs's Avatar
    Join Date
    Feb 2005
    Location
    Buffalo NY
    Posts
    2,446

    Default

    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"

  5. #5
    Board Regular dbmathis's Avatar
    Join Date
    Sep 2002
    Location
    Austin, Texas USA
    Posts
    1,064

    Default

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

    Runtim error 9:

    Subscript out of range.
    After all this is over, all that will really have mattered is how we treated each other.

    Excel Version: 2007
    OS: Windows 7

  6. #6
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default

    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

  7. #7
    Board Regular dbmathis's Avatar
    Join Date
    Sep 2002
    Location
    Austin, Texas USA
    Posts
    1,064

    Default

    parry, THANK YOU!
    After all this is over, all that will really have mattered is how we treated each other.

    Excel Version: 2007
    OS: Windows 7

  8. #8
    Board Regular XLGibbs's Avatar
    Join Date
    Feb 2005
    Location
    Buffalo NY
    Posts
    2,446

    Default

    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.

  9. #9
    Board Regular dbmathis's Avatar
    Join Date
    Sep 2002
    Location
    Austin, Texas USA
    Posts
    1,064

    Default

    Gibbs, that you too.
    After all this is over, all that will really have mattered is how we treated each other.

    Excel Version: 2007
    OS: Windows 7

  10. #10
    Board Regular dbmathis's Avatar
    Join Date
    Sep 2002
    Location
    Austin, Texas USA
    Posts
    1,064

    Default

    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.

    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.
    After all this is over, all that will really have mattered is how we treated each other.

    Excel Version: 2007
    OS: Windows 7

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com