Consulting

Results 1 to 5 of 5

Thread: Solved: Protect/Unprotect Cells with VBA

  1. #1

    Solved: Protect/Unprotect Cells with VBA

    I am creating a form in Excel and need some help.

    I would like to lock cells depending on whether a check box is true or false. I wrote some code that accomplishes this, but when I protect the sheet, I get an error because the sheet is protected.

    In essence, I only want a user to be able to enter data if a check box is true. If the check box is false, I want the cells to be locked.

    I hope I explained that right.

    Please Help.

    Ross

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Try something like this:

    [vba]
    Option Explicit

    Sub Macro1()

    ActiveSheet.Protect Password:="MyPass", UserInterFaceOnly:=True
    If ActiveSheet.CheckBox1.Value = True Then
    Range("A1:B5").Locked = False
    Else
    Range("A1:B5").Locked = True
    End If

    End Sub
    [/vba]

    I am assuming you are using the ActiveX CheckBox. If you are using the Forms CheckBox the check the value of the Linked Cell.

    Also the If statement can be reduced to this:
    [vba]
    Range("A1:B5").Locked = Not ActiveSheet.CheckBox1.Value
    [/vba]

  3. #3
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    So the error you are getting is when the user checks/unchecks the box, is that right? I assume that the code to toggle the "lock" on the cell.

    Try doing this:

    [vba]ActiveSheet.Unprotect
    'your regular code goes here
    ActiveSheet.Protect[/vba]

    The other way to do this would be to put a Workbook_Open procedure in, which loops through each require sheet, and runs something like this:

    [vba]ActiveSheet.Protect userinterfaceonly:=True[/vba]

    This would stop users from hitting your sheets, but let's macros do their thing. The only problem (and reason for the workbook_open routine) is that this setting disappears when you close the workbook.

    HTH,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  4. #4
    Thanks to you both.

    That worked Great!

    Ross

  5. #5
    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
  •