What is the best method to extract data from Comment Box

  • Thread starter Thread starter Charlotte
  • Start date Start date
C

Charlotte

Is there an easy way to do a 'Select all' on a comment box? I've
inherited a spreadsheet that has many comment boxes that contain pages
and pages of text in each box. I want to move the data contained in
these comment boxes to an Access memo field. What is the best way to
extract this data from the comment box? I'll be fine with manually
doing a cut/paste, but 'CTRL-A' for 'Select All' doesn't seem to work
with these comment boxes.
 
Hi Charlotte,

If you click inside the comment and press Ctrl+Home and then Shift+Ctrl+End
you get all the text.

Here is a macro you can run to take all the comments in your spreadsheet and
put them into cell in column A of a sheet named Sheet1. If may not help you
but...
Comment often contain hard carrage returns so check the cells to see if the
are really empty. You may also want to manually set the row height for Sheet1
before you run the macro.

Sub GetComments()
Dim cell As Range
Dim ws As Worksheet
Dim I As Long, Y As String
I = 1
On Error Resume Next
For Each ws In Worksheets
ws.Activate
Selection.SpecialCells(xlCellTypeComments).Select
MsgBox Err.Number
If Err.Number = 0 Then
For Each cell In Selection
Sheets("Sheet1").Cells(I, 1) = cell.Comment.Text
Y = cell.Comment.Text
I = I + 1
Next cell
Else
Err.Clear
End If
Next ws
End Sub
 
I have tried this Debra macro and can't get it to work. It keeps telling me
"no comments found" but there is one there. what am I missing? I have typed
the macro exactly as it is listed.

Thanks.
 
Hi,

I'm not sure what you are trying to do but you can right-click
on the cell that contains the comment and select 'Edit Comment'
then highlight the text in the comment box and copy and paste it to
wherever you need it. Since you are talking about macros you
may also be talking about batch copying of the comments.

If that is the case then ignore this post, but you haven't given
a great deal of detail here.

Cheers
Martin
 
Merry Xmas.
I have just tested that macro successfully. Instead of typing it>COPY/paste
it. Try again
 
Back
Top