Adjusting comment box size by Macro

[yshridhar]

Hi all
How to adjust comment box size and the font of the comment by macro? The
following is the macro.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wsCmt As Worksheet
Dim strCmt As String
Dim rngCmt As Range
Dim ar As Integer

Set wsCmt = Worksheets("rcpt")
Set rngCmt = wsCmt.Range("ndata")
ar = ActiveCell.Row

' Clear Comments
On Error Resume Next
Selection.SpecialCells(xlCellTypeComments).ClearComments

' Extracting the comment value
On Error GoTo endit
Select Case Val(ActiveCell.Column)
Case 2: strCmt = Application.WorksheetFunction.VLookup(Range("b" &
ar), rngCmt, 2, 0)
GoTo feedisplay
Case 9: strCmt = Application.WorksheetFunction.VLookup(Range("b" &
ar), rngCmt, 8, 0)
GoTo feedisplay
Case 10: strCmt = Application.WorksheetFunction.VLookup(Range("b" &
ar), rngCmt, 9, 0)
GoTo feedisplay
Case 11: strCmt = Application.WorksheetFunction.VLookup(Range("b" &
ar), rngCmt, 10, 0)
GoTo feedisplay
Case 12: strCmt = Application.WorksheetFunction.VLookup(Range("b" &
ar), rngCmt, 11, 0)
GoTo feedisplay
Case 13: strCmt = Application.WorksheetFunction.VLookup(Range("b" &
ar), rngCmt, 12, 0)
GoTo feedisplay
Case 14: strCmt = Application.WorksheetFunction.VLookup(Range("b" &
ar), rngCmt, 13, 0)
GoTo feedisplay
Case 15: strCmt = Application.WorksheetFunction.VLookup(Range("b" &
ar), rngCmt, 14, 0)
GoTo feedisplay

Case Else: Exit Sub
End Select
' Display fee balances in comments
feedisplay:
ActiveCell.AddComment CStr(strCmt)
ActiveCell.Comment.Visible = True

Exit Sub
endit:

End Sub


Thanks in advance.
With regards
Sreedhar

 

[Roger Govier]

Hi

take a look at code written by Debra Dalgleish and Dave Peterson at
http://www.contextures.com/xlcomments03.html#Resize

 

[yshridhar]

Thanks Roger. The article is very useful for me.
With regards
Sreedhar