Comment Box Sizing

  • Thread starter Thread starter weh3
  • Start date Start date
W

weh3

Have worksheet in which data entry is driven by userform
selections. A couple of forms have triggers that auto
generate and insert comments into various cells of the
worksheet. I am trying to come up with code that will
format ALL comments to the same ScaleWidth and
ScaleHeight either as they are created OR globally as a
default size defined by me.

Anyone have an idea of how best to handle this, or do I
seek the "impossible". Much thanks for any help.
 
I don't know whether this will work for you, but you can
call the following function, pass it a cell address as a
string and it will "Auto-size" the comment. This reduces
the comment box to the minimum required for display:

Function CommentAutoSize(argAddress As String)

Range(argAddress).Comment.Visible = True
Range(argAddress).Comment.Shape.Select True
Selection.AutoSize = True
Range(argAddress).Comment.Visible = False

End Function

HTH, but if this is way off base, please disregard.
 
Back
Top