Display comments as data

S

sonika

thanks a lot , this worked exactly as required, the comment go
converted to data

Now just one more thing . is the vice versa possible i.e if i have dat
in column A can it be shown as comment in column B.

the reason i need this is bcos i am shifting some data with thei
comments to MS access(Hence the need to convert omments to data)
processing them and again converting them to excel (the need to conver
data back to comments
 
M

Max

Now just one more thing . is the vice versa possible i.e if i have data
in column A can it be shown as comment in column B.
Click to expand...

Think there's a Sub AddComments() [reproduced below]
in the same Dave M's page given earlier:
http://www.mvps.org/dmcritchie/excel/ccomment.htm

which will do the above. It's titled under the header: Macro to populate
comments in a range with text values of another range (#addcomments)

----------------- begin vba ---------
Sub AddComments()
'Posted by Dave Ramage, 2001-04-11, misc,
Dim rngComments, rngCells As Range
Dim lCnt As Long

'get user to select range
Set rngComments = Application.InputBox(prompt:="Select" _
& " range containing comments text:", _
Title:="Add comments: Step 1 of 2", Type:=8)
'was Cancel pressed?
If rngComments Is Nothing Then Exit Sub

Set rngCells = Application.InputBox(prompt:="Select cells to update:", _
Title:="Add comments: Step 2 of 2", _
Type:=8)
If rngCells Is Nothing Then Exit Sub

'are ranges the same size?
If rngCells.Areas(1).Cells.Count <> rngComments.Areas(1).Cells.Count Then
MsgBox ("Ranges must be the same size!")
Exit Sub
End If

'add comments
For lCnt = 1 To rngCells.Areas(1).Cells.Count
'does the cell already have a comment?
If rngCells.Areas(1).Cells(lCnt).Comment Is Nothing Then
'no comment, so add one
rngCells.Areas(1).Cells(lCnt).AddComment _
rngComments.Areas(1).Cells(lCnt).Text
Else
'already comment, so delete then add
rngCells.Areas(1).Cells(lCnt).Comment.Delete
rngCells.Areas(1).Cells(lCnt).AddComment _
rngComments.Areas(1).Cells(lCnt).Text
End If
Next lCnt
End Sub
----------------- end vba ---------
 
M

Max

Just some implementation steps for the Sub AddComments() ..

Press Alt+F11 to go to VBE
Click Insert > Module
Copy > paste everything within the dotted lines
("begin vba" till "end vba")
into the whitespace on the right

Press Alt+Q to get back to Excel

Now to play around with the sub
------------------------------------------------
In a test worksheet filled with text values
(source comments) in say, A1:A8

Press Alt+F8 to bring up the Macro dialog

Select "AddComments" > Run

Answer the 1st prompt by selecting
the source range, i.e. A1:A8 > OK

Answer the 2nd prompt by selecting
the target range, say B1:B8 > OK

That's it !
 
Top