Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do I print the contents of a commented cell along with the cell comments at the end of a worksheet? I know how to print the comments at the end, but is it possible to add in the cell contents?

Example:

Cell E96 in my worksheet contains: "Remove storm windows"

The comment for cell E96: "6 windows plus 1 small...say .5 hours each = 3.5 hours"

When I print a worksheet with comments appended after the worksheet I get:

Cell | E96

Comment | 6 windows plus 1 small...say .5 hours each = 3.5 hours

I'd like to add the contents of the commented cell there too, like this :

(or something similar, I'm flexible)

Cell | E96 Remove storm windows

Comment | 6 windows plus 1 small...say .5 hours each = 3.5 hours

share|improve this question
    
I don't believe you will able to find a way to alter how Excel prints out comments. The best you will be able to do is VBA code to create a custom list of commented cells, i.e., a cell's address, contents, and comment. Take a look at the code here, which looks like it does pretty much all of what you want. – chuff Jan 31 '13 at 5:20
up vote 0 down vote accepted

I took the code from chuffs link and corrected some errors.

The macro extracts the cell address, cell value, author, comment text of all commented cells and writes them to a new worksheet. This sheet can be printed separately with your normal sheet.

  • open your workbook and press ALT+F11
  • insert the code below into sheet1 or a new module
  • close the VBA editor and go back to the sheet with your comments
  • press ALT+F8 and execute the macro

Sub ListComments()
Application.ScreenUpdating = False

    Dim X As Long, RngName As String, newwks As Worksheet, curwks As Worksheet

    Set curwks = ActiveSheet
    If curwks.Comments.Count Then

        Set newwks = Worksheets.Add
        newwks.Range("A1").Resize(1, 4) = Array("Address", "Cell Value", "Author", "Comment")

        On Error Resume Next
        For X = 1 To curwks.Comments.Count
            With curwks.Comments.Item(X)
                newwks.Range("A1").Offset(X, 0).Resize(1, 4) = _
                    Array(.Parent.Address(False, False), .Parent.Value, .Author, .Text)
            End With
        Next
    End If

Application.ScreenUpdating = True
End Sub
share|improve this answer

I'm not sure if this will help you but you could try CTRL V it is supposed to copy formulas, values etc. I don't know if this will do what you want but thought you might want to try it if you are not already aware of this function.

share|improve this answer
    
This doesn't help solve the question – Marcus Chan Jan 31 '13 at 2:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .