Home > Net >  Either of the checkbox can only be true
Either of the checkbox can only be true

Time:03-14

Situation I have two form control check boxes. I am trying to write a code that will allow only either of them to be true.

Two form control check box

my code is

Sub CheckBox2_Click()
  If CheckBox1.Enabled = True Then
    CheckBox2.Enabled = False
  Else
     If CheckBox2.Enabled = True Then
         CheckBox1.Enabled = False
     End If
  End If    
End Sub

I have this code in module and have assigned the same macro for both the checkboxes. I get run-time error 424. I beleive this is very basic problem but I unable to dela with it. Thank you

CodePudding user response:

Please, test the following way. Form check boxes do not have a click event, as ActiveX ones have. You should associate the next sub to both of them. The check boxes I tested, have their names as "Check Box 1" and "Check Box 2". You have to change yours according to the reality in your case, Please, copy the next code in a standard module and then associate it to both used check boxes:

Option Explicit

Sub FormCheckBoxChange()
     If ActiveSheet.CheckBoxes(Application.Caller).value = 1 Then
        Select Case Application.Caller
           Case "Check Box 1": ActiveSheet.CheckBoxes("Check Box 2").value = -4146
           Case "Check Box 2": ActiveSheet.CheckBoxes("Check Box 1").value = -4146
        End Select
     End If
End Sub

Use instead of the used check box names, the appropriate ones for your case.

In case of Form text boxes, their value is not True and False as in case of ActiveX ones. It is 1 and -4146...

CodePudding user response:

Are you sure you want to enable/disable the checkboxes.

Following code makes sure that either one of both boxes is checked.


Public Sub checkbox2_onClick()

Dim oCb1 As Object
Set oCb1 = Table1.Shapes("Checkbox1").OLEFormat.Object

Dim oCb2 As Object
Set oCb2 = Table1.Shapes("Checkbox2").OLEFormat.Object

If oCb2.Value = 1 Then oCb1.Value = 0


End Sub

Public Sub checkbox1_onClick()
Dim oCb1 As Object
Set oCb1 = Table1.Shapes("Checkbox1").OLEFormat.Object

Dim oCb2 As Object
Set oCb2 = Table1.Shapes("Checkbox2").OLEFormat.Object

If oCb1.Value = 1 Then oCb2.Value = 0

End Sub
  • Related