View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
KC VBA Qns KC VBA Qns is offline
external usenet poster
 
Posts: 14
Default Comment Box Placement

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