Deselecting a checkbox when another is checked

Question

I have two checkboxes, form controls, if I select one, the other one is deselected. I cannot use ActiveX controls because there are many other checkboxes from other sheets are form controls. I cannot use option button because I need to get "true" "false" value.

I used this code below:

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

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

But it shows

Run-time error '424', Object required

I am very new to VBA and still learning. I have no idea to fix it, please help me!! Thank you very much!!

Save the name of the current checkbox as a variable, then run the macro from: https://stackoverflow.com/questions/18927684/unselect-all-checkboxes-from-excel-workbook-with-vba-macro . After all are unselected, use the saved variable for your current checkbox and check the marker. — Cyril, Mar 14 '18 at 20:16
There's lots of information about differences between ActiveX controls and Form controls, and how to code with them, at [my answer here](https://stackoverflow.com/a/49263001/8112776). — ashleedawg, Mar 17 '18 at 15:51

Dick Kusleika · Answer 1 · 2018-03-19T15:55:24.590

0

You say you cannot use ActiveX, but your code is ActiveX events. MSForms.Checkboxes don't have events. With forms controls, you assign a macro to the control. You can assign the same macro to multiple controls and use Application.Caller to determine which control called it.

Sub CheckBoxClicked()

    Dim chkClicked As CheckBox
    Dim chkOther As CheckBox

    Set chkClicked = Sheet1.CheckBoxes(Application.Caller)

    If chkClicked.Enabled Then
        If Application.Caller = "Check Box 1" Then
            Set chkOther = Sheet1.CheckBoxes("Check Box 2")
        Else
            Set chkOther = Sheet1.CheckBoxes("Check Box 1")
        End If

        chkOther.Value = False
        chkOther.Enabled = False

    End If

End Sub

This goes in a standard module, not the sheet module.

Checkbox is deprecated, but still works for me. You can do the same thing using the non-deprecated Shape object.

Sub CheckBoxClicked()

    Dim chkClicked As Shape
    Dim chkOther As Shape

    Set chkClicked = Sheet1.Shapes(Application.Caller)

    If chkClicked.OLEFormat.Object.Enabled Then
        If Application.Caller = "Check Box 1" Then
            Set chkOther = Sheet1.Shapes("Check Box 2")
        Else
            Set chkOther = Sheet1.Shapes("Check Box 1")
        End If

        chkOther.OLEFormat.Object.Value = False
        chkOther.OLEFormat.Object.Enabled = False

    End If

End Sub

Edit

If you only care about disabling Check Box 2 when Check Box 1 is clicked, the code becomes simpler.

Sub CheckBoxClicked()

    Dim chkClicked As CheckBox
    Dim chkOther As CheckBox

    Set chkClicked = Sheet1.CheckBoxes("Check Box 1")
    Set chkOther = Sheet1.CheckBoxes("Check Box 2")

    If chkClicked.Enabled Then

        chkOther.Value = False
        chkOther.Enabled = False

    End If

End Sub

You would only assign this macro to Check Box 1.

edited Mar 19 '18 at 15:55

answered Mar 14 '18 at 20:37

Dick Kusleika

32,673
4
52
73

Hello Dick, thank you for your reply! I have done very little on VBA coding before so it is very hard when I need it at work. I tried to run your code but I got another error "Run-time error '1004': Method 'CheckBoxes' of object '_Worksheet' failed". Could you please explain this a little? Thank you very much!! – Steven Mar 15 '18 at 01:06
Select your checkboxes on the worksheet and look in the name box to get the name. If they're named something other than "Check Box 1" and "Check Box 2", you'll have to change those values in the code. Also, my checkboxes were on a worksheet with codename Sheet1. You need to change that part of the code to your worksheet's codenam (or reference your sheet some other way). – Dick Kusleika Mar 15 '18 at 13:30
The checkboxes names and sheet name are exactly the same with yours. When I debug, it went to this line: Set chkClicked = Sheet1.CheckBoxes(Application.Caller) I am very sorry to bother you so much, but I couldn't find other solutions online. – Steven Mar 15 '18 at 13:46
Make sure your code is in a standard module and not sheet or other kind of module. Also, what version of Excel and what operating system are you using. `Checkboxes` is a deprecated method, but it still works - I use it all the time. – Dick Kusleika Mar 17 '18 at 14:47
My excel is 2010,operating system is Windows 7. In the VBA, I inserted a Module, put the new code in and run, then an error message came out: "Run-time error '-2147352571 (80020005):', the item with the specified name wasn't found". I assigned the macro to the "Check box 1", only "Check box 1" can be selected, "Check box 2" cannot. Is it possible that run the code without using Application.Caller? Because when I debug the code, the Application.Caller line became yellow. – Steven Mar 19 '18 at 13:51
Yes, if you only care about cb1. See my edits. I'm still not sure why `Application.Caller` doesn't work. – Dick Kusleika Mar 19 '18 at 15:55

score 0 · Answer 2 · answered Oct 11 '18 at 14:37

Please use this code

Private Sub CheckBox1_Click()
If Me.CheckBox1.Value = True Then
Me.CheckBox2.Value = False
Me.CheckBox2.Enabled = False
Else
Me.CheckBox2.Enabled = True
End If
End Sub

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

Deselecting a checkbox when another is checked

2 Answers2