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

[reemt]

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

 

[ColinKJ]

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:

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

 

[reemt]

Thanks Colin,

I will check and will let u know if it worked...