Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lock Name LIst (2000)

    Cells B1:B30 will be the cells for employee names. I would like to put some serious protection into any name being changed or deleted.

    I want to start with the entire spreadsheet (including cells B1:B30) locked and the sheet protected. That way if a user does not enable macros, they will not be able to type in cells B1:B30. If the user clicks "Enable Macros" upon opening the workbook, then I want to have only the blank cells in B1:B30 be unlocked and the sheet remain protected. (ex: If no names exist yet in B1:B30, then I would want all 30 cells to be unlocked. If 5 names are already listed, then the remaining 25 cells would be unlocked).

    Then, what I would like to have happen with the blank cells in B1:B30 is after a name is entered have a pop-up message asking the user to confirm the name/spelling (etc.). After they click ok to confirm the name, I want that specific cell locked, and the spreadsheet protected again. After a name has been confirmed, the user should never be able to change it or delete it.

    After all 30 spots are full, then B1:B30 would permanently remained locked whether or not macros are enabled.

    I hope this is clear... Thanks!!

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

    Re: Lock Name LIst (2000)

    Right-click the sheet tab and select View Code from the popup menu.
    Copy the following code into the module that appears:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("B1:B30")) Is Nothing Then
    Application.EnableEvents = False
    Me.Unprotect ' "password"
    For Each oCell In Intersect(Target, Range("B1:B30"))
    If Not oCell = "" Then
    If MsgBox("Are you sure that '" & oCell & "' is correct?", _
    vbQuestion + vbYesNo) = vbYes Then
    oCell.Locked = True
    End If
    End If
    Next oCell
    Me.Protect ' "password"
    Application.EnableEvents = True
    End If
    End Sub

    I have commented out the password. If you have protected the worksheet with a password, substitute the correct password and uncomment it.

  3. #3
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock Name LIst (2000)

    This doesn't seem to quite work as desired. Let me try to clarify... It's not always easy to explain through type.

    UPON OPENING EXCEL:
    If user clicks 'Enable Macros' Then
    Check cell Range B1:B30 for blanks
    Uprotect sheet, unlock any blank cells in Range B1:B30, Protect Sheet (all-non blank cells remain locked)
    End If

    AFTER USER HAS ENABLED MACROS:
    Since the non-blank cells are the only cells in B1:B30 that are unprotected, a user may enter data (an employee name). The current code works except it locks all cells in B1:B30 after the user enters a value in any one cell. It should only lock that cell that was just changed. Also, if they click No (the type is not correct) it still locks all cells in B1:B30 instead of allowing the user to correct the name.

    Thanks for the help! Hope this clarifies a little better!

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

    Re: Lock Name LIst (2000)

    I don't know what you did, but the code doesn't work that way for me. It only locks a cell if the user confirms that it is correct after he/she entered something into it. Other cells in the range B1:B30 are not affected. If the user clicks No, no cells are locked.

    Try again with the attached workbook.

  5. #5
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock Name LIst (2000)

    That's really strange, your attachment works fine... Don't know what I did.

    Also, I am going to lock all cells B1:B30 on the Workbook_BeforeClose(). Do you know a way when a user enables macros to run a procedure that will unlock only those cells that are blank in B1:B30?

    The reason I want to do this is so in case the user clicks Disable Macros (or if their security is set to high) they will not be able to change any cells B1:B30. That way they will be forced to enable macros if they need to add a name. That way it will be subject to the verification and protection you just showed me.

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

    Re: Lock Name LIst (2000)

    You could put the following line in the Workbook_Open event procedure:

    Worksheets("Name List").Range("B1:B30").SpecialCells(xlCellTypeBlan ks).Locked = False

  7. #7
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock Name LIst (2000)

    All works perfectly now!! Thanks!

    Oh, I was missing the Option Explicit to start. That's why the code wasn't quite working correctly.

    Thanks again for the help!

Posting Permissions

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