About Us

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Lost my Paste	Mike R	Excel Discussion (Misc queries)	11	August 29th 05 12:22 AM
Displaying value of specific cell within a range, with IF function...?	Steve	Excel Discussion (Misc queries)	1	January 14th 05 02:23 AM
Amount or Numbers in Words	ron	New Users to Excel	6	December 24th 04 07:32 PM
I cant use englisch function names in a swedich version of excel	PE	Excel Discussion (Misc queries)	2	December 7th 04 01:00 AM
Counting Function Dilemma	Simon Lloyd	Excel Worksheet Functions	0	November 8th 04 03:13 PM

Menu

About Us