Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Protection and Comments (Excel 97 SR2)

    I have created an Excel spreadsheet which I leave protected to prevent formulae being accidentally deleted or overwritten by users, but this also prevents comments being inserted, which the users need to do. Is there a way to permit comments to be inserted without unprotecting the sheet?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Protection and Comments (Excel 97 SR2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Murgatroyd

    In addition to the comments you got already, if the users will enter comments in a specific place, you can unprotect that particular section of the worksheet and allow the users to use it without protecting/unprotecting each time.

    Hope this helps.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection and Comments (Excel 97 SR2)

    Adding cell comments is disabled, even on unprotected cells, when the workbook is protected.
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection and Comments (Excel 97 SR2)

    The only way that I can think of would be to use a macro. The macro would prompt the user for the comment, unprotect the workbook, insert the comment, then protect the workbook again.

    Edited to add example below:

    <pre>Public Sub AddComment()
    Dim strComment As String
    strComment = InputBox("Please enter comment")
    If strComment <> "" Then
    Worksheets("Sheet1").Unprotect ("MyPassword")
    On Error Resume Next
    ActiveCell.AddComment
    On Error GoTo 0
    ActiveCell.Comment.Text ActiveCell.Comment.Text & " " & strComment
    Worksheets("Sheet1").Protect ("MyPassword")
    End If
    End Sub
    </pre>


    That code will add the text entered to any comment already attached to the cell.
    Legare Coleman

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Protection and Comments (Excel 97 SR2)

    Hi,
    I don't know if it was the same in 97, but in Excel 2000, if you protect the sheet but uncheck the Objects box in the Protect For... dialog, you can still insert comments but your formulae cannot be altered.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Protection and Comments (Excel 97 SR2)

    Rory, Excel 97 behaves similarly.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Protection and Comments (Excel 97 SR2)

    Thanks to all for your prompt and helpful replies, which are much appreciated.

Posting Permissions

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