Thanks! I had the hardest time with that!

"Debra Dalgleish" wrote:

The following code will add a comment to the cell that was changed:

'=========================
Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim oldtext As String
Dim newtext As String

With Target
On Error Resume Next
oldtext = .Comment.Text
If Err < 0 Then
.AddComment
End If
On Error GoTo 0
newtext = oldtext & " Changed to " & .Text & _
" by " & Application.UserName & " at " & Now & vbLf
With .Comment
.Text newtext
.Visible = True
.Shape.TextFrame.AutoSize = True
.Visible = True
End With
End With
End Sub
'============================


JJ wrote:
Alan,

The range("A1") is so that when I have the function cover the whole sheet
(ex. =worksheet_change(1:65536), it will only add a comment to the active
cell. I had previously tried your proposed solution, but when a new comment
is to be appended, the box multiplies by the number, in your case 1.3,
instead of resizing. I know how to have a specific width, but I would like
the width of the box to vary depending on the comment length. Any other
suggestions?

"Alan" wrote:


"JJ" wrote in message
...

I have created a function that allows me to see a change everytime
there is a change on the worksheet. A comment box will appear and
continue to append the changes, unlike the "track changes function".
I can not seem to figure out why my comment box will not
autosize......I have attached my code. Does anyone know what I am
doing wrong?

Function Worksheet_Change(ByVal Target1 As Excel.range)
With activecell.Offset(0, 0).range("A1")
On Error Resume Next
oldtext = .Comment.Text
If Err < 0 Then .AddComment
newtext = oldtext & "Changed to " & .Text & _
" by " & Application.UserName & " at " & Now & vbLf
.Comment.Text newtext
.Comment.Visible = True
.Comment.Shape.Select True
.autosize = True
.Comment.Visible = True
End With
End Function


Hi,

It appears from reading your code that the autosize method is being
applied to the cell (range) and not the comment itself.

You need to reference the shape property of the comment object to
return a shape object and then re-size that something like:

ActiveSheet.Range("D13").Comment.Shape.ScaleWidt h 1.3, msoFalse,
msoScaleFromTopLeft

The 1.3 was chosen randomly.

BTW, what does the .range("A1") on the end of the first line do?


HTH,

Alan.









--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html