PDA

View Full Version : [SOLVED] Protect Sheet but still insert comments



bay_analyst
08-08-2004, 10:06 AM
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
08-08-2004, 11:08 AM
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
08-08-2004, 12:13 PM
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:


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:


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.


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


cheers!
Jeroen

Jacob Hilderbrand
08-08-2004, 08:32 PM
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.



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.

bay_analyst
08-09-2004, 05:04 AM
Thanks Jacob!!!

Your solution worked perfectly.

Darlene

Jacob Hilderbrand
08-09-2004, 05:42 AM
You're Welcome :)

Take Care