blackie42
08-19-2009, 07:54 AM
Hi,
Got the following to prompt a user to insert comment on changing cell contents within a range (that code held in worksheet change event calling the below code)
Sub InsertCommentsSelection()
Dim sCmt As String
Dim rCell As Range
sCmt = InputBox( _
Prompt:="Enter Comment to Add", Title:="Comment to Add")
If sCmt = "" Then
MsgBox "No comment added"
Else
For Each rCell In Selection
With rCell
.ClearComments
.AddComment
.Comment.Text Text:=sCmt
.Comment.Visible = False
End With
Next
End If
Set rCell = Nothing
End Sub
I'd like to be able to prefix the comment with the application Username (as it does if its added without VBA) but can't seem to get it to work.
Also is it possible (and how if so) to be able to update the comment if the cell contents change again (without wiping off the original comment)
many thanks for your help
Jon
Got the following to prompt a user to insert comment on changing cell contents within a range (that code held in worksheet change event calling the below code)
Sub InsertCommentsSelection()
Dim sCmt As String
Dim rCell As Range
sCmt = InputBox( _
Prompt:="Enter Comment to Add", Title:="Comment to Add")
If sCmt = "" Then
MsgBox "No comment added"
Else
For Each rCell In Selection
With rCell
.ClearComments
.AddComment
.Comment.Text Text:=sCmt
.Comment.Visible = False
End With
Next
End If
Set rCell = Nothing
End Sub
I'd like to be able to prefix the comment with the application Username (as it does if its added without VBA) but can't seem to get it to work.
Also is it possible (and how if so) to be able to update the comment if the cell contents change again (without wiping off the original comment)
many thanks for your help
Jon