adding text to a comment

kotting

New Member
Joined
Mar 18, 2002
Messages
17
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?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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

Active Member
Joined
Aug 21, 2002
Messages
255
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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

Active Member
Joined
Aug 21, 2002
Messages
255
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

New Member
Joined
Mar 18, 2002
Messages
17
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
 

Forum statistics

Threads
1,144,120
Messages
5,722,596
Members
422,447
Latest member
knopp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top