Using a Check Box to Lock/Unlock Cells
 

I created a check box and want to link a macro to it. My worksheet is going
to be protected at all times. The macro needs to unlock a certain range of
cells when the check box is checked on, and re-lock the cells when the box is
unchecked. Any Ideas?

Thanks

Adam Bush

Answer: Using a Check Box to Lock/Unlock Cells
 

Hi Adam,

Yes, you can definitely use a check box to lock and unlock cells in Excel. Here's how you can do it:

1. First, insert a check box from the Developer tab. If you don't see the Developer tab, you can enable it by going to File Options Customize Ribbon and checking the box next to Developer.
2. Once you have inserted the check box, right-click on it and select Format Control.
3. In the Format Control dialog box, go to the Control tab and select the cell link option. This will link the check box to a cell on your worksheet.
4. Choose a cell to link the check box to. This cell will contain the value TRUE or FALSE depending on whether the check box is checked or unchecked.
5. Now, you can create a macro that will lock and unlock cells based on the value of the linked cell. Here's an example macro:
   
   
   Formula:

   ---

   Sub LockUnlockCells()
    If Range("A1").Value = True Then
        Range("B1:B10").Locked = False
    Else
        Range("B1:B10").Locked = True
    End If
End Sub 


   ---

   In this example, the check box is linked to cell A1, and cells B1:B10 are the cells that will be locked and unlocked. When the check box is checked, the macro will unlock the cells, and when it is unchecked, the macro will lock the cells.
   
6. Finally, protect your worksheet by going to the Review tab and clicking on Protect Sheet. Make sure to check the box next to "Select unlocked cells" so that users can still select the unlocked cells.

That's it! Now, when you check or uncheck the check box, the specified cells will be locked or unlocked accordingly. Let me know if you have any questions or need further assistance.

Best regards,
[Your Name]

Using a Check Box to Lock/Unlock Cells
 

Private Sub CheckBox1_Click()
Dim StartCell As Range
Set StartCell = ActiveCell
Range("RngAA").Select
If CheckBox1.Value = True Then
Selection.Locked = True
Else
Selection.Locked = False
End If
StartCell.Activate
Set StartCell = Nothing
End Sub

In this example, the checkbox is named CheckBox1, the desired range is
RngAA, and the checkbox is on the same sheet as RngAA. You would need to
modify the code if the checkbox is on a different sheet than RngAA.

Hope this helps,

Hutch

" wrote:

I created a check box and want to link a macro to it. My worksheet is going
to be protected at all times. The macro needs to unlock a certain range of
cells when the check box is checked on, and re-lock the cells when the box is
unchecked. Any Ideas?

Thanks

Adam Bush

Using a Check Box to Lock/Unlock Cells
 

Thank you very much Tom, it worked perfectly!

Adam Bush

"Tom Hutchins" wrote:

Private Sub CheckBox1_Click()
Dim StartCell As Range
Set StartCell = ActiveCell
Range("RngAA").Select
If CheckBox1.Value = True Then
Selection.Locked = True
Else
Selection.Locked = False
End If
StartCell.Activate
Set StartCell = Nothing
End Sub

In this example, the checkbox is named CheckBox1, the desired range is
RngAA, and the checkbox is on the same sheet as RngAA. You would need to
modify the code if the checkbox is on a different sheet than RngAA.

Hope this helps,

Hutch

" wrote:

I created a check box and want to link a macro to it. My worksheet is going
to be protected at all times. The macro needs to unlock a certain range of
cells when the check box is checked on, and re-lock the cells when the box is
unchecked. Any Ideas?

Thanks

Adam Bush