| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding text to comment
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 -- Traa Dy Liooar Jock |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding text to comment
Change
Comment.Visible = True Comment.Shape.Select to Target.Comment.Visible = True Target.Comment.Shape.Select If this post helps click Yes --------------- Jacob Skaria "Jock" wrote: 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 -- Traa Dy Liooar Jock |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding text to comment
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 wrote: 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 -- Traa Dy Liooar Jock -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding text to comment
How cool is that. Even better than the real thing. :)
Many thanks -- Traa Dy Liooar Jock "Dave Peterson" wrote: 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 wrote: 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 -- Traa Dy Liooar Jock -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding text to comment
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding text to comment
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding text to comment
This seems promising.
Thanks Dave, I'll test tomorrow - off home now!! Yippee -- 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding text to comment
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding text to comment
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding a note/comment | Excel Discussion (Misc queries) | |||
| Adding comment text - linefeed visible - why? | Excel Programming | |||
| Adding a comment to a cell in VBA?! | Excel Programming | |||
| Adding default comment text | Excel Discussion (Misc queries) | |||
| Adding Comment to cell | Excel Programming | |||
Hybrid Mode
