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

"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.ScaleWidth 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.

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.ScaleWidth 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.

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

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

"Debra Dalgleish" wrote in message
...

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
'============================


Hi Debra,

Out of interest, is there any way to hide the cell comment marker?
Obviously we can hide the comment itself, but what about the red
triangle marker?

Also, can we avoid the comment 'popping' up when the cell is selected
with a 'hidden' comment?

Both of those are user annoyance issues rather than anything more
fundamental.

Thanks,

Alan.

You can hide all the comment indicators:

Manually -- Choose ToolsOptions, and on the View tab, for Comments,
choose None

Or, programmatically, e.g.:

Application.DisplayCommentIndicator = xlNoIndicator

If the markers are hidden the comments won't pop up


Alan wrote:
"Debra Dalgleish" wrote in message
...

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
'============================



Hi Debra,

Out of interest, is there any way to hide the cell comment marker?
Obviously we can hide the comment itself, but what about the red
triangle marker?

Also, can we avoid the comment 'popping' up when the cell is selected
with a 'hidden' comment?

Both of those are user annoyance issues rather than anything more
fundamental.

Thanks,

Alan.





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

"Debra Dalgleish" wrote in message
...

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
'============================


Another comment / question:

Wouldn't it be more useful to use the FORMULA property of the range
(cell), rather than the TEXT property on this line:

newtext = oldtext & " Changed to " & .Formula & _


Is there any downside to using the FORMULA property that I am not
foreseeing?

Alan.

Using .Formula instead of .Text shouldn't create any problems.
What happened when you tried it? <g

Alan wrote:
"Debra Dalgleish" wrote in message
...

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
'============================



Another comment / question:

Wouldn't it be more useful to use the FORMULA property of the range
(cell), rather than the TEXT property on this line:

newtext = oldtext & " Changed to " & .Formula & _


Is there any downside to using the FORMULA property that I am not
foreseeing?

Alan.





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