Comment Box Placement

  • Thread starter Thread starter KC VBA Qns
  • Start date Start date
K

KC VBA Qns

Hihi,
I kinda pretty much researched historical posts on comment box. I am
trying to use VBA to reset the placement of the existing comment box
back to just next to the cell (from far away location), but I dont
seem to find any discussion on this.
Rgds,
 
GOT IT.

base on: http://www.contextures.com/xlcomments03.html#Reset

Sub KC_Comment_Rebox()
For Each CELL In Selection
If Not CELL.Comment Is Nothing Then
CELL.Comment.Shape.Placement = xlMove
CELL.Comment.Shape.Top = _
CELL.Comment.Parent.Top - 7.5
CELL.Comment.Shape.Left = _
CELL.Comment.Parent.Offset(0, 1).Left + 11.25
CSH = CELL.Comment.Shape.Height
CSW = CELL.Comment.Shape.Width
If CSH < 55.5 * 0.9 Or CSH > 55.5 * 1.1 Or _
CSW < 96 * 0.9 Or CSW > 96 * 1.1 Then _
CELL.Comment.Shape.TextFrame.AutoSize = True
End If
Next CELL
End Sub
 
Back
Top