Well, this is not foolproof (the user can not enable macros when the workbook is open); but, assuming that doesn't happen, you can do the following to preserve the existing comments. Place each section of code into the indicated location.
Put in a Module (Insert/Module from VB editor)
============================================================
Dim CellAddress As String
Dim CommentOnEntry As String
Put in Sheet module (select sheet from list on left of VB editor)
============================================================
Private Sub Worksheet_Activate()
CellAddress = ActiveCell.Address
If Not ActiveCell.Comment Is Nothing Then
CommentOnEntry = ActiveCell.Comment.Text
End If
End Sub
Private Sub Worksheet_Deactivate()
If CommentOnEntry <> "" Then
Range(CellAddress).Comment.Text CommentOnEntry
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If CommentOnEntry <> "" Then
Range(CellAddress).Comment.Text CommentOnEntry
End If
CellAddress = Target.Address
If Not ActiveCell.Comment Is Nothing Then
CommentOnEntry = Target.Comment.Text
End If
End Sub
Put in the Workbook module (ThisWorkbook from list on left of VB editor)
============================================================
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If CommentOnEntry <> "" Then
Range(CellAddress).Comment.Text CommentOnEntry
End If
End Sub
After doing that, go back to the worksheet, select a cell with a comment and edit the comment. Now, when you click into another cell, switch to another sheet or save the workbook, the original comment will be restored.
--
Rick (MVP - Excel)
"Adam at Star Packaging" <(E-Mail Removed)> wrote in message news:2725E221-E573-4DAB-B0FB-(E-Mail Removed)...
> Anytime a comment is made, the cell can be right-click "edit comment."
>
> I'm wondering if there is a way to keep the Now() portion of the comment to
> become uneditable.
>
> Again, without using the protect document option.
>
>
> "JLGWhiz" wrote:
>
>> I am not sure what you mean by the "text portion" but if you mean you want
>> to protect the code itself, then in the VBE, click Tools>VBA Project
>> Properties>Protection and enter a password that locks out any casual
>> observer from the code. It cannot be accessed by a user from Excel, it can
>> only be accessed through the VBE with a password thereafter.
>>
>>
>> "Adam at Star Packaging" <(E-Mail Removed)>
>> wrote in message news:0165FC46-E3BD-4BF3-B27F-(E-Mail Removed)...
>> >I have the following macro set, but I would like to make the text portion
>> >of
>> > it uneditable. Is there a code to place in the macro for this?
>> >
>> > (Protect document will not work for us in this case)
>> >
>> > Sub CommentAddPic()
>> > 'adds formatted comment with picture
>> > Dim cmt As Comment
>> > Set cmt = ActiveCell.Comment
>> > If cmt Is Nothing Then
>> > ActiveCell.AddComment Text:="On" & Now()
>> > Set cmt = ActiveCell.Comment
>> > With cmt.Shape.TextFrame.Characters.Font
>> > .Bold = False
>> > .ColorIndex = 0
>> > End With
>> > cmt.Shape.Fill.UserPicture "C:\Documents and Settings\All
>> > Users\Documents\My Pictures\Sample Pictures\sig.jpg"
>> > End If
>> > End Sub
>> >
>> > Thanks!
>> >
>>
>>
>>
|
Similar Threads
