Consulting

Results 1 to 6 of 6

Thread: Protect Sheet but still insert comments

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Location
    Surrey, B.C.
    Posts
    8
    Location

    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

  2. #2
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

  3. #3
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    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

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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.

  5. #5
    VBAX Regular
    Joined
    Jul 2004
    Location
    Surrey, B.C.
    Posts
    8
    Location
    Thanks Jacob!!!

    Your solution worked perfectly.

    Darlene

  6. #6
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You're Welcome

    Take Care

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •