What does the application.enableevents do? Does it keep it from going into
an infinite loop when you reset the cell value with "see comment"?

"Rick Rothstein" wrote:

How about something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Target.Count < 1 Then Exit Sub
If .Value < "See Comment" Then
If .Row 1 And .Column = 14 Then
.ClearComments
If .Value < "" Then
.AddComment .Value
.Comment.Shape.Width = 95
.Application.EnableEvents = False
.Value = "See Comment"
.Application.EnableEvents = True
End If
End If
End If
End With
End Sub

--
Rick (MVP - Excel)


"DCPan" wrote in message
...
The code is located in the worksheet for worksheet_change.

For some strange reason, when I use the target in my code, it fails,
that's
why I handed Target.AddressLocal off to strRange.

Thanks!

"Rick Rothstein" wrote:

Where is this code located... in an event procedure like
Worksheet_Change?
What is strRange and, if it is what I think it is, why are you using
Range(strRange) instead of Target in your code?

--
Rick (MVP - Excel)


"DCPan" wrote in message
...
Hi all,

The script I attached below will place what is typed in the cell into a
comment box, then replace the cell with "see comments" and delete the
comment
box when "see comments" is erased.

Now, the question is...I have auto-size turned on...but why does the
comment
box only format itself "length wise"?

Can I specify a comment box width and only have it autosize vertically?

Thanks!
_________________

If Target.Row 1 And Target.Column = 14 _
And Range(strRange).Value < "See Comment" _
And Range(strRange).Value < "" Then

Range(strRange).Value = "See Comment"
Range(strRange).Select
Range(strRange).AddComment
Range(strRange).Comment.Visible = True
Range(strRange).Comment.Text Text:=strComment
Range(strRange).Comment.Shape.Select True
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With
Range(strRange).Comment.Visible = False
Range(strRange).Select
'Range("A" & (Target.Row + 1)).Select

Else

If Target.Row 1 And Target.Column = 14 _
And Range(strRange).Value = "" Then

Range(strRange).Select
Range(strRange).ClearComments

End If

Range(strRange).Select
'Range("A" & (Target.Row + 1)).Select

End If