Solved: Protect Sheet but still insert comments

[bay_analyst]

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

[Anne Troy]

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.

[roos01]

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:
[VBA]
Private Sub Workbook_Open()
Application.OnKey "{F12}", "InsertComment"
End Sub
Private Sub Workbook_Close()
Application.OnKey "{F12}"
End Sub
[/VBA]

the next amcro goes into a new module:
[VBA]
Sub InsertComment()
ActiveSheet.Unprotect
Selection.AddComment
'ActiveSheet.Protect
End Sub
[/VBA]

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.
[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
ActiveSheet.Protect
End Sub
[/VBA]

cheers!
Jeroen

[Jacob Hilderbrand]

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.

[VBA]
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveMenu
End Sub
Private Sub Workbook_Open()
Call CreateMenu
End Sub
[/VBA]

Place this code in a Standard Module.

[VBA]
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
[/VBA]

Change the "Password" to your actual password.

[bay_analyst]

Thanks Jacob!!!

Your solution worked perfectly.

Darlene

[Jacob Hilderbrand]

You're Welcome

Take Care