adding text to a comment

[kotting]

Sub privateprotection()
Dim logIn As Variant
logIn = Sheets("Config").Range("LoggedInAs")
If ActiveCell.Value <> logIn Or Empty <> ActiveCell.Value Then
If MsgBox("This is not you! Continue?", vbYesNo) = vbYes Then
'ThisWorksheet.Unprotect Password:="locked"
ActiveCell.AddComment
ActiveCell.NoteText logIn
'ThisWorksheet.Protect Password:="locked"
Else: MsgBox "No problem - Action Cancelled"
End If
End If
End Sub



People must login to access the workbook. [LoggedInAs]
Changing an empty cell is permitted.
Changing a cell that matches the [LoggedInAs] is permitted.
Changing a cell that contains other data generates a comment attached to the cell containing the [LoggedInAs] identity.



I’ve run into a problem.

This works ok but I want to add the possibility to ADD text to the comment box. I want to keep the text (preferably with a date). Is it possible for users to edit the comment without being able to delete the text already there?

 

[Tom Urtis]

Yes it is possible to do that. You seem to be familiar with VBA so the code below is meant to provide an example of a way to go, though you will need to modify it for your needs.

A couple points:

(1)
In your posted code, you are adding a comment. That's fine if no comment already exists, but as you probably know, you'll get an error if you try to add a comment to a cell that already has one. That's the reason for the error trap.

(2)
Usually, comments are identified by their object index number ("Comments(1)", etc.). That is pretty much thrown out the window if you want to add or edit a comment in an active cell, as your code suggests. So, my code suggestion below takes the approach that the edited comment should start with the same text (including the date), and an input box can be the medium for adding to the text if the user wants, while keeping the original text intact.

See if this gets you closer to what you want. Run this macro by itself, then see how it can apply to your situation, if at all. Hope it helps.


Sub CommentEditPractice()
Dim cQ As Integer
cQ = MsgBox("Do you want to add to the comment of this active cell?", 36, "Comment customization possible...")
Select Case cQ
Case Is = 7
MsgBox "No problem, we'll leave the comment as it is." & vbCrLf & _
"Please click OK to resume.", 64, "Comment will not be edited."
Exit Sub
Case Is = 6
MsgBox "Cool. Click OK and enter your additional comments" & vbCrLf & _
"in the input box.", 64, "One comment edit, coming up!!"
Dim cAdd As String
cAdd = InputBox("Please enter your additional comments below:", "Comment edit box.")
On Error Resume Next
ActiveCell.AddComment.Text "Hello, today's date is " & Date & ". " & cAdd
End Select
End Sub

 

[rikrak]

Tom,

I got curious when i saw the NoteText-property in OP's code.
---------------------------
ActiveCell.AddComment
ActiveCell.NoteText logIn
---------------------------
Why is he adding a comment this way?
So i tried this:
<pre>
Sub Add_Note()
LoggedInAs = "Rikrak"
If Len(ActiveCell.NoteText) = 0 Then
ActiveCell.NoteText Time() & LoggedInAs
Else
ActiveCell.NoteText ActiveCell.NoteText & Chr(10) & Time() & LoggedInAs
End If
End Sub
</pre>
And it works fine.
I looks like i can do without ActiveCell.AddComment.
What's the difference between these two methods of adding a comment?

 

[Tom Urtis]

rikrak --

Good question, as there may be very little difference, depending on the user interface techniques the OP wants to present to the end user.


Here's my understanding of this issue, and anyone, please correct me if I'm wrong:

Any cell can have a comment assigned to it. As you point out, the comment can be set or manipulated with the NoteText method. For Excel97 and beyond, what we now know as comments used to be called notes, and they are all controlled by the modernized Comment object. With the method AddComment, comment text can also be manipulated. I used it because the OP used it.

Two thoughts:

(1)
Because NoteText is a method which is a parameter of a character string that can accommodate up to 255 characters, there are 2 parameters whereby the start and end position of the comment can be given, which means you can edit comments that are longer than 255 characters.

(2)
Sort of an aside but related, there are residual actions leftover from the prehistoric spreadsheet & programming language days that can still apply to code today. An example is the comments you put in your VBA code, which 99.99999% of us do by preceding it with an apostrophe. You can also type "Rem" to mark comments (must be at the beginning of the comment line), which Microsoft probably held over for the sake of compatibility with the habits of our code-writing founding fathers & mothers. As usual, there is more than one way to skin an Excel cat.

Not sure if this answers your question completely. Maybe the OP can tell us if he's seen any difference if he tried both methods.

 

[rikrak]

Thanks Tom.
When adding a comment to a cell i conclude that both methods can give the same result, util someone comes up with a difference.
I did a search on this board and found 4 posts containing notetext and 29 containing addcomment. Lasw10 was the only member that used notetext.
I think adding/manipulating a comment by using .notetext is much more simple then .addcomment.

Kotting,
Try this peace of code to prevent users delete the complete comment or change the original contents. It stores the original commenttext and compares it to the new text after a user selects another cell. If the 2 differ the original text is placed in front of the current contents. Hope it works.<pre>
Dim lastcell As Range
Dim lastcellNotetext As String

Private Sub Workbook_Activate()

' save current commenttext of activecell for later use
Set lastcell = ActiveCell
lastcellNotetext = ActiveCell.NoteText

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

'check if commenttext has changed
If lastcell.NoteText<> lastcellNotetext Then
'check if user removed original text
If InStr(lastcell.NoteText, lastcellNotetext) = 0 Then
'restore original text + changes
lastcell.NoteText lastcellNotetext & Chr(10) & lastcell.NoteText
End If
End If

' save (updated) commenttext for later use
Set lastcell = Target
lastcellNotetext = Target.NoteText

End Sub</pre>
This message was edited by rikrak on 2002-10-21 18:03

 

[kotting]

Thanks for the great advice. After I have a chance to play around with it, I will post another reply. And thanks again this will really help.

Kotting