Excel VBA - Run-Time Error '424': Object Required

0
votes

I have the following code for a Check Box (ActiveX Control) using Excel 2013:

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

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

What it does is simply uncheck Box 2 if I check Box 1, and vice versa. But if I use the above code for a Check Box (Form Control), I get a "Run-time error '424': Object required" error message. Does anyone know of a solution?

UPDATE: The same code above that I tried using in a file that I got from Bytes somehow worked. Since I'm a newbie in VBA, I think I'm gonna have to sit down and study how Excel, Macros & VBA work together. Once I find out the source of the problem (that technically I created myself), I'll post an answer here as to how I figured it out. Thanks to everyone that posted comments & replies. I really appreciate it!

3
vbaexcel
you probably need to specify the userForm like userForm.Checkbox1.Value – pokemon_Man
Where is this code? Is it in the userform? – BenShelton
Maybe use a real 'radio button' instead of trying to mimic the behavior...? – user4039065
Both sets of boxes are inserted directly in cells. I'm not using a UserForm whatsoever (although I might give it a shot if nothing else works). I also haven't tried Option Buttons, but I might give them a shot too. But the question here is, why does the above code work fine for ActiveX Controls but not for Form Controls? – Jay Lee
Use OptionButtons. Remember to set the GroupName property to be the same for all OptionButtons you want to be part of the same group (obvious I know, but if you have more than 1 group of options, you'll want to give each group a unique GroupName). – sous2817

3 Answers

0
votes

There's a UX (user experience) issue here.

  • Checkboxes are for when you need the user to pick one or more values.
  • OptionButtons are for when you need the user to pick one of many mutually exclusive values.

So what you need is OptionButtons (ActiveX controls) - make sure they have the same GroupName, and you'll get the exact same behavior you're trying to achieve, without writing a single line of code:

option buttons (ActiveX controls)

And then in your VBA code you can access them all by their name (e.g. OptionButton1) to verify their value.

Form controls vs ActiveX controls

"Form Controls" are just shapes that you can assign to a macro (e.g. do something when the user clicks it) - you can't access them and their properties in code like you would with ActiveX controls.

With ActiveX controls you can access them as if they were global object variables, and do what you did:

CheckBox2.Value = True

The biggest hint you get, is IntelliSense: with ActiveX controls, when you type the dot (.) after the control's name, you get a dropdown in the editor, telling you that the VBE understands what you're talking about; when you do the same with a "form control", you don't get that dropdown, and the VBE does NOT understand what you're talking about - and if you persist and run that code, you'll run into run-time errors like you did.

0
votes

You refer to a Form control by name or index into the Checkboxes collection:

Activesheet.CheckBoxes(1).Value

for example.

0
votes

You use the incorrect Event listener. Try this

Private Sub CheckBox1_Change()
    If CheckBox1 = True Then
        CheckBox2 = False
    Else
        CheckBox2 = True
    End If        
End Sub

Here is the list event listeners for checkbox enter image description here