Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.