You could move the guts of the the code to a general module and then call it
when your existing worksheet_change event fires (and pass it the required info).

But my guess is that the code you added does something that just skips the
comment stuff.

I don't know what your other stuff does, but in general...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub 'single cell at a time
End If

If Not (Intersect(Target, Me.Range("x:x")) Is Nothing) Then
'column X changed (or whatever you need to check)
MsgBox "A1 changed!" 'do the stuff for this other range
ElseIf Not (Intersect(Target, Me.Range("H:H")) Is Nothing) Then
MsgBox "column H Changed!" 'the comment stuff would go here
End If

End Sub

Jock wrote:

Hi Dave,
your code did work until recently; the Before_DoubleClick version still
works fine. As my workbook is a 'work in progress' it is changing daily, it
seems. It's more or less finished now but your Worksheet_Change adaptation
(below) now does nothing. It has probably been affected by some of the other
code which has been added to the same Worksheet_Change event. Incedentally, I
couldn't use the Option Explicit as this code is tagged on the end of others.
So, my question is this: can your code be placed as a sub in a module and
called from a line in the worksheet_change code? Is that a way around my
problem?

Thanks for your help.

--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myStr As String
Dim myPfx As String

If Target.Cells.Count 1 Then 'now it maybe necessary
Exit Sub
End If

If Application.Intersect(Target, Me.Range("H:H")) Is Nothing Then
'do nothing, not in column H
Else
If UCase((Target.Value)) = "T" Then
'delete existing comment if there is one
If Target.Comment Is Nothing Then
'no comment, do nothing
Else
Target.Comment.Delete
End If

myPfx = "Transferred by " & "UserName" & " on " _
& Format(Date, "dd/mmm") & "."

myStr = Trim(InputBox(Prompt:="Enter old reference number", _
Title:=myPfx))

If myStr = "" Then
'do nothing
Else
myStr = vbLf & "Old reference number - " & myStr
End If

Target.AddComment myPfx & myStr
Target.Comment.Shape.TextFrame.AutoSize = True
End If
End If

End Sub



Jock wrote:

I have tried to adapt your code from a double click event to a worksheet
change for use elsewhere in the worksheet. However, the code hangs mid flow
and has to be reset.
Also, Column "H" has a dropdown list and regardless of what was chosen, "T"
would be inserted by the code hence my If statement: this is probably the
probem.
Here's my variation:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myStr As String
Dim myPfx As String

If Target.Cells.Count 1 Then 'probably not necessary
Exit Sub
End If

If Application.Intersect(Target, Me.Range("H:H")) Is Nothing Then
'do nothing, not in column H
Else
'Cancel = True 'stop editing in cell from starting

Application.EnableEvents = False
If (Target.Value) = "T" Then
Application.EnableEvents = True

'delete existing comment if there is one
If Target.Comment Is Nothing Then
'no comment, do nothing
Else
Target.Comment.Delete
End If

myPfx = "Transferred by " & UserName & " on " & Format(Date,
"dd/mmm") & ". Old reference number - "
myStr = Trim(InputBox(Prompt:="Enter old reference number",
Title:=myPfx))
End If
If myStr = "" Then
'do nothing
Else
'go to next line in comment
myStr = vbLf & Trim(myStr)
End If

Target.AddComment myPfx & myStr
Target.Comment.Shape.TextFrame.AutoSize = True


End If

End Sub
--
Traa Dy Liooar

Jock

--

Dave Peterson


--

Dave Peterson