Comments Made Simple(r)

http://blog.livedoor.jp/andrewe/archives/cat_415504.html

"Doug Glancy kindly suggested making my code run more like a regular userform in the comments of my last post.

'So this is what I came up with. Enter the text as before by pushing the OK button.



'Then choose to add another line or continue by inserting the comment.



'You can then choose to keep the comment visible or hide it from view. With a bit of practice you can enter lines and comments quite quickly. A shortcut would be a really good idea if you use it a lot.

'Here's the new code.

Sub AddComment()
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, Dscn As Long
If TypeName(Selection) <> "Range" Then Exit Sub
AddLine:
nwLne = InputBox("Enter line " & LneCnt + 1 & " of your comment text." & vbNewLine & vbNewLine & _
"Leave blank or push Cancel to exit.", "Please write your comment")
If LneCnt > 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg = nwLne
LneCnt = LneCnt + 1
If nwLne = "" Then
Exit Sub
Else
Dscn = MsgBox("Do you want to add another line? " & vbNewLine & vbNewLine _
& "Push No to insert the comment.", vbYesNo, "Add another line?")
If Dscn = vbYes Then GoTo AddLine
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment
.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg
With Selection.ShapeRange
.Shadow.Visible = msoFalse
.ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft
.Adjustments.Item(1) = 0.25
End With
.Shape.TextFrame.Autosize = True
Dscn = MsgBox("Do you want the comment to remain visible? ", _
vbYesNo, "Keep comment visible?")
If Dscn = vbNo Then .Visible = False
End With
.Select
End With
Application.ScreenUpdating = True
End If
End Sub

'Hmm...me like :-)

'Update: Andy Pope sent me a file to show an even better job can be done with a Multiline Textbox. There's no need to push buttons to add new lines, just use the Enter key. This also has a great advantage in that you see all of the text, not just one line at a time) When finished you can enter the text, by pushing Tab, Enter (This enables the Ok button to insert the comment)

'Here's a picture of it in action.



'You can download his file here.

More good code for me to study. Thanks also to Jon Peltier for suggesting the same thing."