Jump to content

Getting a Cells Comments


Rbreb13
 Share

Recommended Posts

Getting a Cells Comments

I'm trying to format an Excel database as a report in Word or Publisher. In addition to the five fields in columns A through E, I have a sixth field consisting of the cell comments from column A. How do I reference those comments? (It would be difficult to cut and paste the comments into open cells because there are so many records.)

What you need to get from each cell in column A is the Text property of the Comment object–possibly minus the Comment object's Author property. (If you don't subtract the Author property, each cell's comment will be preceded by the name of the person who entered the comment, followed by a colon.)

The simplest way to get this information into your Word or Publisher report would be to extract it first into column F of your worksheet using a macro. From Excel, press Alt-F11 to open the Visual Basic Editor. Choose Insert | Module, and then, in the code window, type the following:

Sub GetComments()

Dim InputRange As Range

Set InputRange =

Application.InputBox _

(prompt:="What range?",

Type:=8)

On Error Resume Next

For Each cell In InputRange

cell.Offset(0, 5) = _

Mid(cell.Comment.Text, _

Len(cell.Comment.Author) + 3)

Next cell

End Sub

Back on the worksheet, choose Tools | Macro | Macros | Run.Select GetComments and click Run. The macro will prompt for a range. Select the range in column A that you want to get comments from, and the macro will plant those comments, sans author, in column F. Note that the comments will land in column F even if you already have data there. Be sure the column is clear before running the macro.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...