Would need to play around with the height / width stuff, assuming you want
to resize.

One technique to add to this excellent idea might be the use of "AutoSize."
Here's one example.

Sub Comment_Add_Formula()
'// = = = = = = = = = = = = = =
'// Within a selection...
'// Adds the Cells Formula to the Cells Comoment
'// If no Formula, adds the text..."No Formula"
'// = = = = = = = = = = = = = =

'//
Dim Cell As Range
Const Msg As String = "No Formula"
'//

On Error Resume Next

If TypeName(Selection) < "Range" Then Exit Sub

For Each Cell In Selection.Cells
With Cell
If .HasFormula Then
If .Comment Is Nothing Then
.AddComment .Formula
Else
.Comment.Text .Formula
End If
Else
If .Comment Is Nothing Then
.AddComment Msg
Else
.Comment.Text Msg
End If
End If
.Comment.Shape.TextFrame.AutoSize = True
.Comment.Visible = True
End With
Next Cell
End Sub


Just an idea. HTH
--
Dana DeLouis
Win XP & Office 2003


"Peter T" <peter_t@discussions wrote in message
...
Hi Max,

Two for the price of one. I wouldn't want a comment if there is no
formula -Test1. But for your needs - Test2.

Sub Test1()
'deletes any comment if no formula
Dim cell As Range, sText As String, cm As Comment
For Each cell In Selection
With cell
Set cm = .Comment
If .HasFormula Then
sText = .Formula
If cm Is Nothing Then Set cm = .AddComment
cm.Visible = False
cm.Text Text:=sText
cm.Shape.Width = Len(sText) * 5 + 10
cm.Shape.Height = .Height * 1.2
ElseIf Not cm Is Nothing Then
cm.Delete
End If
End With
Set cm = Nothing
Next
End Sub

Sub Test2()
'include comment w/out formula
Dim cell As Range, sText As String, cm As Comment
Const s As String = "No Formula"
For Each cell In Selection
With cell
Set cm = .Comment
If cm Is Nothing Then Set cm = .AddComment
If .HasFormula Then
sText = .Formula
Else: sText = s
End If
cm.Visible = False
cm.Text Text:=sText
cm.Shape.Width = Len(sText) * 5 + 10
cm.Shape.Height = .Height * 1.2
End With
Set cm = Nothing
Next
End Sub

Would need to play around with the height / width stuff, assuming you want
to resize.

Regards,
Peter T


"Max" wrote in message
...
.. what I'm after is for a sub to transfer the formulas as text into the
selected cells' comment boxes for safekeep/documentation purposes ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----