adding text to comment

[Jock]

Hi,
The following code will add a comment to a cell in column "AW" when it is
double clicked.
The user will need to add text to the comment so I put in the final two
lines of code hoping to make the comment appear on screen ready to be edited
but that doesn't happen.
What do I need to add/change for this to happen?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("AW:AW")) Is Nothing Then
ActiveCell = "P"
'Application.EnableEvents = False
Target.AddComment " passed by " & UserName() & " on " & Format(Date,
"dd/mmm")
Target.Comment.Shape.TextFrame.AutoSize = True
End If
Comment.Visible = True
Comment.Shape.Select

ws_exit:
Application.EnableEvents = True
End Sub

 

[Jacob Skaria]

Change

Comment.Visible = True
Comment.Shape.Select

to

Target.Comment.Visible = True
Target.Comment.Shape.Select


If this post helps click Yes

 

[Dave Peterson]

I would just ask the user for their comment:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, _
Cancel As Boolean)

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("AW:AW")) Is Nothing Then
'do nothing, not in column AW
Else
Cancel = True 'stop editing in cell from starting

Application.EnableEvents = False
Target.Value = "P"
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 = "Passed by " & UserName & " on " & Format(Date, "dd/mmm")
myStr = Trim(InputBox(Prompt:="Enter your comment", Title:=myPfx))

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

 

[Jock]

How cool is that. Even better than the real thing.
Many thanks

 

[Jock]

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

 

[Dave Peterson]

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]

This seems promising.
Thanks Dave, I'll test tomorrow - off home now!!
Yippee

 

[Jock]

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.

 

[Dave Peterson]

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

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.