code to make a comment macro uneditable

[Adam at Star Packaging]

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!

 

[JLGWhiz]

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]

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.

 

[Rick Rothstein]

Well, this is not foolproof (the user can not enable macros when the workbook is open); but, assuming that doesn't happen, this can be used to preserve existing comments...

 

[Rick Rothstein]

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.