Programming Comments in Excel 2003

[Guest]

Hello,

I am programming a userform to input and edit records in a spreadsheet. I
have a couple of textboxes in the form that I am using to add comment boxes
to certain cells. I am trying to find a way to set the comment boxes format
to automatic size within the userform's code. This way long comments will be
entirely visible when the mouse hovers over the comment.

Any help or suggestions would be very helpful.

Thanks,


-Brandon

 

[Guest]

Here is a function you can use to auto size cell comments (copy to standard
code module):

Public Function CommentAutoSizeRange(argAddress As Range)
'FUNCTION AUTO-SIZES THE COMMENT IN THE CELL SUPPLIED;
argAddress.Comment.Visible = True
argAddress.Comment.Shape.Select True
Selection.AutoSize = True
argAddress.Comment.Visible = False
End Function

Call the above like so:

Private Sub CommandButton1_Click()
Dim rngCell As Range
Set rngCell = ActiveCell
Call CommentAutoSizeRange(rngCell)
End Sub

This function resizes all comments in the sheet:

Public Function CommentsAutoSizeAll()
'AUTO-SIZE ALL COMMENTS IN THE ACTIVESHEET;
Dim cmt As Comment
Dim cmts As Comments
Set cmts = ActiveSheet.Comments
For Each cmt In cmts
cmt.Shape.TextFrame.AutoSize = True
Next
End Function

HTH