Specify Size of Comment


Posted by JAF on February 22, 2000 3:29 AM

I have a macro which tracks changes to cells within a worksheet by adding comments to changed cells. . . .

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
here = Target.Address
newnum = Target.Value
oldnum = Sheets("historical data").Range(here).Value
If oldnum = "" Then GoTo line99
oldcomment = Target.Comment.Text
Target.AddComment
Target.Comment.Text oldcomment & "Modified: " & Date & " " & Time & " by " & Application.UserName & ". Previous value was " & oldnum & Chr(10)
line99:
Sheets("historical data").Range(here).Value = newnum
End Sub

This works nicely, but the only slight problem is that the default comment size is too small.

Is there any way to either change the default size of a comment or to specify the required size when the comment is created?


JAF



Posted by Celia on February 22, 2000 7:09 AM

JAF
One way is to use the macro recorder to find out the code required to change the comment format to your requirements.
Celia