Consulting

Results 1 to 6 of 6

Thread: VBA to Print the Comments of a single cell

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    373
    Location

    VBA to Print the Comments of a single cell

    I am trying to print the Comments of a single cell.
    Also, Is there a way to copy it to another sheet and print it from there.
    It would seem to be a shape at that point.
    I tried the latter, but the shape does not allow you to copy it.

    This bit works

    Range("AE9").Select
            ActiveCell.Comment.Visible = True
            Range("AE9").Comment.Shape.Select True
    But this bit DOES NOT WORK

    Selection.Copy
            Sheets("Sheet2").Range("A1").Paste

  2. #2
    Range("AE9).Select
    Selection.Copy
    Sheets("Shee2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

  3. #3
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    373
    Location
    arnelgp: Hi Thanks.

    I can copy the Comments to the other sheet; The problem is that when I try to print, the error says:
    " Excel did not find anything to print "

    This code does show the comments, but I cant figure out how to print them.

    Sub CopyComments()
    ActiveSheet.Range("AE9").Select
    ActiveSheet.Range("AE9").Comment.Visible = True
    Selection.Copy
    
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    
    End Sub
    How can I copy the text from a single cells' comments ?

  4. #4
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    373
    Location
    This will get the Comments of a single cell for printing. It's NOT Text, but an image.

    Sub CopyACellComments()
    
    ActiveSheet.Range("AE9").Select
    ActiveSheet.Range("AE9").Comment.Visible = True
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    
    
    With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
        ActiveSheet.PageSetup.PrintArea = ""
        With ActiveSheet.PageSetup
            
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintInPlace
            .PrintQuality = 600      
            .PrintErrors = xlPrintErrorsDisplayed       
            .FirstPage.RightFooter.Text = ""
        End With
        ActiveWindow.SelectedSheets.PrintPreview
    End Sub
    If there's a way to get comments from a single cell as Text, I'll like to know.

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,377
    try:
    Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("AE9").Comment.Text
    Now print Sheet2 normally.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    373
    Location
    p45cal: THANKS !

    That works great.

Posting Permissions

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