Protect Sheet but still insert comments

I have a spreadsheet that I have protected, some of the cells are unlocked so others can enter their numbers, the rest are locked so they can screw up the formulas.

Is there any way to allow them to put insert a comment into the cells that are unlocked. The insert comment is greyed out when the protection is on the sheet.

Darlene

Though I don't code, and can't write the code for you, perhaps you could have a separate button that allows the user to insert comments. Select a cell, click the button. The button *ought* to (IMHO) bring up a text input user form and then, on "OK", it unprotects the sheet, places the text input into the comment of that cell, and reprotects the sheet.

?

If that was okay with you, I'm sure someone could code it.

there is some nasty way to get around this.
first you have to create a hot key which enables the insert comment action for you.
then you have to create the insert comment macro.
and at last you have to create a macro when the selection changes the sheet is set to protection again like this.

place these macros in the ThisWorkbook section:

Code:

---

Private Sub Workbook_Open()
Application.OnKey "{F12}", "InsertComment"
End Sub
Private Sub Workbook_Close()
Application.OnKey "{F12}"
End Sub

---

the next macro goes into a new module:

Code:

---

Sub InsertComment()
ActiveSheet.Unprotect
Selection.AddComment
'ActiveSheet.Protect
End Sub

---

the last macro make it possible to edit the workbook and then when another cell is selected it is set to protected again. it should be placed in the workbook where comments insertion is allowed.

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
ActiveSheet.Protect
End Sub

---

cheers!
Jeroen

We can be a little tricky with the Insert Menu. Note this can be done with the right click menu as well.

Place this code in the ThisWorkbook code section.


Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveMenu
End Sub
Private Sub Workbook_Open()
Call CreateMenu
End Sub


Place this code in a Standard Module.

Code:

---

Option Explicit
Sub CreateMenu()
Dim MenuObject As CommandBarPopup
Dim MenuItem As Object
Dim Position As Long
    Call RemoveMenu
    On Error Resume Next
    Set MenuObject = Application.CommandBars("Worksheet Menu Bar").Controls("&Insert")
    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton, Before:=11, Temporary:=True)
    With MenuObject
    .Controls("Co&mment").Visible = False
    End With
    With MenuItem
    .Caption = "Comment..."
    .OnAction = "InsertComment"
    .FaceId = 2031
    End With
    On Error GoTo 0
End Sub

Sub RemoveMenu()
Dim MenuObject As CommandBarPopup
Dim MenuItem As Object
Dim Position As Long
On Error Resume Next
    Set MenuObject = Application.CommandBars("Worksheet Menu Bar").Controls("&Insert")
    With MenuObject
    .Controls("Comment...").Delete
    .Controls("Co&mment").Visible = True
    End With
    On Error GoTo 0
End Sub

Sub InsertComment()
Dim Default As String
Dim Comment As String
Dim Prompt As String
Dim Title As String
    Prompt = "Input the text for the comment."
    Title = "Text Input"
ActiveSheet.Unprotect Password:="Password"
    With ActiveCell
    On Error Resume Next
    .AddComment
    Default = .Comment.Text
    .Comment.Delete
    On Error GoTo 0
    Comment = InputBox(Prompt, Title, Default)
    .AddComment
    .Comment.Visible = False
    .Comment.Text Text:=Comment
    End With
    ActiveSheet.Protect Password:="Password"
End Sub

---

Change the "Password" to your actual password.

Thanks Jacob!!!

Your solution worked perfectly.

Darlene

You're Welcome :)

Take Care