Locking cells with linked check boxes conditional to a ticked check box

reemt

New Member
Joined
Jan 6, 2011
Messages
28
Hi,

i have a protected sheet with unlocked cells C35 to C38. these cells are actually tick boxes (linked to the cells). i need a VBA code to do the following:

when the tick box in cell C35 is ticked (i.e, the selected option is TRUE); i need the remaining cells C36 to C38 to be locked so that their relative tick boxes cannot be ticked.
and the same to all the other tick boxes. i.e, when C36 is ticked, then C35,C37 and C38 will be locked (cannot be ticked); etc for all the 4 tick boxes. (and i think what the code should include is that when ticking one box the other boxes will be unticked automatically or something like that)

and i noted something that when a cell that has a tick box is locked, the box actually can be ticked but the text will be "False", it won't be changed to "TRUE". I need to avoid the box being ticked when a cell is locked also.

i'm not familiar with VBA , can anyone help me in that please.

thanks :)
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
Hi reemt,

You would need something like this in each of the CheckBox Click Events, in this example the CheckBoxes are linked to C1 to C4:

Code:
Private Sub CheckBox1_Click()
'First Un-Protect the Sheet
Sheets("Sheet1").Unprotect
If CheckBox1 = True Then
'Lock the other 3 cells
    Cells(1, 3) = "TRUE"
    Cells(2, 3).Locked = True
    Cells(3, 3).Locked = True
    Cells(4, 3).Locked = True
'Disable the other 3 CheckBoxes
    CheckBox2.Enabled = False
    CheckBox3.Enabled = False
    CheckBox4.Enabled = False
End If
If CheckBox1 = False Then
'Unlock the other 3 cells
Cells(1, 3) = "FALSE"
    Cells(2, 3).Locked = False
    Cells(3, 3).Locked = False
    Cells(4, 3).Locked = False
'Enable the other CheckBoxes
    CheckBox2.Enabled = True
    CheckBox3.Enabled = True
    CheckBox4.Enabled = True
End If
'Protect the Sheet Again
Sheets("Sheet1").Protect
End Sub

You would change the cell and CheckBox references according to the appropriate CheckBox.

Regards

Colin
 

Watch MrExcel Video

Forum statistics

Threads
1,126,893
Messages
5,621,486
Members
415,844
Latest member
Reda Fouad Ramzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top