Copy Cell to Comment

  • Thread starter Thread starter Gary''s Student
  • Start date Start date
G

Gary''s Student

I have some cells formatted as Text. I need to insert a comment in these
cells and copy the value in the cell to the comment. I need the formatting
in the cell to be copied along with the text. So, for example, if the text
in the cell is bold, then the text in the comment should also be bold, etc.
I am now using:

Sub SetComment()
With Selection
.AddComment
.Comment.Visible = True
.Comment.Text Text:=.Value
End With
End Sub

This creates the comment and copies the text, but it does not copy the
formatting.

How do I get the formatting to copy in addition to the value?
 
Sub SetComment2()
Dim cel As Range
Dim cm As Comment
Set cel = ActiveCell
With cel
cel.ClearComments
Set cm = .AddComment
cm.Text Text:=.Value
End With
cm.Visible = True
With cm.Shape.TextFrame.Characters.Font
.Bold = cel.Font.Bold
' etc
End With
End Sub

Regards,
Peter T
 
Thank you!
--
Gary''s Student - gsnu200823


Peter T said:
Sub SetComment2()
Dim cel As Range
Dim cm As Comment
Set cel = ActiveCell
With cel
cel.ClearComments
Set cm = .AddComment
cm.Text Text:=.Value
End With
cm.Visible = True
With cm.Shape.TextFrame.Characters.Font
.Bold = cel.Font.Bold
' etc
End With
End Sub

Regards,
Peter T
Click to expand...
 
Back
Top