Transcribing cell formulas into their comment boxes

[Max]

Hi guys,

Say, B1:E1 contain various formulas. I select B1:E1 and would like to
"transcribe" the formulas for the selected range directly into the
respective comment boxes, i.e. the comment boxes for B1:E1.

And if there happens to be no formulas in certain cells within the selected
range, I'll like a simple phrase: "No formula" to be written in the cpmment
box for those cells

Thanks

 

[Max]

... what I'm after is for a sub to transfer the formulas as text into the
selected cells' comment boxes for safekeep/documentation purposes ..

 

[Peter T]

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]

Two for the price of one. ..

Great !! Many thanks, Peter. Runs smooth as silk.
It's a fantastic bargain, and I appreciate it <bg>

 

[Dana DeLouis]

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

 

[Max]

Sub Comment_Add_Formula()

Thanks for the nice add-on, Dana !
It runs great (with autosizing) !

I did amend it a bit to hide the comments, though <g> ..
..Comment.Visible = False

 

[Peter T]

Dana,
Autosize, oh yes, much better than those height / width guestimates.

Only for personal aesthetic preference, I'd also include:
..Comment.Shape.Shadow.Visible = msoFalse

Max,

It's a fantastic bargain, and I appreciate it <bg>

Just disposing of old stock. New versions will include Dana's Autosize, at
full price of course!

Regards,
Peter T

 

[Dana DeLouis]

I think that's a great idea about removing the shadows from the comments. I
didn't think about that. It does look a little better! Thanks :>)

--
Dana DeLouis
Win XP & Office 2003

Dana,
Autosize, oh yes, much better than those height / width guestimates.

Only for personal aesthetic preference, I'd also include:
.Comment.Shape.Shadow.Visible = msoFalse

<snip>

 

[Max]

Thanks for the nice add-ons, Dana and Peter !

To suit my use, I amended a bit to hide the comments:
..Comment.Visible = False

Terrific stuff ! Cheers.