Allow only one checkbox to be enabled

[Dokat]

Hi,

I have an excel worksheet with 3 checkboxes. I want only one checkbox to be selected at a time.

I have below code in standard module and assigned it to all 3 checkboxes. However when i run the code i get Run Time Error 438 Object doesnt' support this property or method error.

Can anyone help me with this issue?

Please see attached file

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox2.Value = False
CheckBox3.Value = False
End If
End Sub

Thanks

 

[Hui]

Change them to Radio Buttons instead
Then add them to a Group box
You’ll get the required behaviour
No code required

 

[Dokat]

Thank you. But i will need them to be a checkbox.

 

[Dokat]

Below code worked for me

Option Explicit
Sub testme()
Dim ThisCBX As CheckBox
Dim CBX As CheckBox
Set ThisCBX = ActiveSheet.CheckBoxes(Application.Caller)
If ThisCBX.Value = xlOn Then
For Each CBX In ActiveSheet.CheckBoxes
If CBX.Name = ThisCBX.Name Then
'do nothing
Else
CBX.Value = xlOff
End If
Next CBX
End If
End Sub

 

[Hui]

This can be tidied up a little

Option Explicit
Sub testme()
Dim ThisCBX As CheckBox, CBX As CheckBox
Set ThisCBX = ActiveSheet.CheckBoxes(Application.Caller)
If ThisCBX.Value = xlOn Then
For Each CBX In ActiveSheet.CheckBoxes
If CBX.Name <> ThisCBX.Name Then CBX.Value = xlOff
Next CBX
End If
End Sub