Comment in a cell

[felix]

Is there a simple way (macro) that copies a value in a
cell and pastes it as a comment of the same cell?
Preferably, the comment box should be re-sized to the size
of the entry. The original value in the cell may remain.
Thank you

 

[Frank Kabel]

Hi
as starting point:

Sub insert_comment()
Dim rng As Range
Set rng = ActiveCell
With rng.AddComment
.Visible = False
.Text rng.Value
End With
End Sub

 

[Guest]

Danke !

I tried the macro, it resulted in an empty comment box and
the error:

Run time error '1004'
Application-defind or object-defined error and the
debugger stops at .Text rng.Value

 

[Frank Kabel]

Hi
probably your active cell was empty. Try the following
(some error checking added):
Sub insert_comment()
Dim rng As Range
Set rng = ActiveCell
On Error Resume Next
rng.Comment.Delete
On Error GoTo 0
If rng.Value <> "" Then
With rng.AddComment
.Visible = False
.Text rng.Value
End With
End With
End Sub