Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Protecting Specific Cells (2002)

    I have data validation based on the contents of cells I2-I4. Is it possible to protect these specific cells and allow changes to remainder of sheet?

    Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protecting Specific Cells (2002)

    Cells are protected by default.

    unprotect all the cells, then you can protected the cells you want protected. then protect the sheet.

    i don't understand why you would want to protect cells with data validation however. It defeats using datavalidation on a protected cell since the cell would be protected and not changable. Data validation is for allowing changes but only particular changes.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting Specific Cells (2002)

    I want to protect the cells that the data validation is based on. My dropdown references these cells in column I. Consequently, I would not want these (my list) to change.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protecting Specific Cells (2002)

    Hi Mitch

    You could try this:

    Right hand click the worksheert tab and select View Code. Assume that the references for the validation are in B1:B10

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Application.Intersect(Target, Range("B1:B10")) Is Nothing Then
    Cells(ActiveCell.Row, 2).Select
    range("A1").select
    End If
    End Sub


    This will mean that when a user clicks any cell in B1:B10 it will instantly move to A1.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting Specific Cells (2002)

    Thanks.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting Specific Cells (2002)

    As Steve wrote:
    - Select all cells (Ctrl+A).
    - Select Format | Cells...
    - Activate the Protection tab.
    - Clear the Locked check box.
    - Click OK.
    - Select the source cells of the validation list.
    - Select Format | Cells...
    - Activate the Protection tab.
    - Ticked the Locked check box.
    - Click OK.
    - Select Tools | Protection | Protect Sheet...
    - Specify what you want the user to be able to do.
    - Specify a password if you want.
    - Click OK.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting Specific Cells (2002)

    Perfect. If I was unclear earlier my apologies to all.

    Hans you are truly the MAN.

    Thanks again for your help. How did you become so knowledgeable about ALL of this?

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

    Re: Protecting Specific Cells (2002)

    That will also make UNDO stop working for the entire sheet.
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protecting Specific Cells (2002)

    Sorry don't understand?
    Jerry

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting Specific Cells (2002)

    Execution of VBA code clears the undo stack in Excel. The code you posted will fire each time the user selects/moves to a different cell, so undo will be disabled almost permanently.

Posting Permissions

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