Form Control Checkboxes (Not ActiveX Controls )

[rameezl17]

Hi Everyone,

I'm still new to coding and looking to learn more. One thing I can't figure out and I'm 100% sure its so easy. I am doing this through a Form Control because I know that ActiveX controls on an excel sheet causes resizing when the screen resolution changes.

So I have a Checkbox that says "Yes?" Named (CheckBox1). The Macro I assigned to it is that when you click on the checkbox it opens up "UserForm1". So the Code is straightforward.

Sub CheckBox1_Click()
UserForm1.Show
End Sub

That works fine, however when I go to uncheck the checkbox in the excel sheet, it causes UserForm1 to open again and then the "Yes?" Checkbox is unchecked. How can I make it so that when I uncheck the checkbox it doesn't show UserForm1, just unchecks the checkbox?

(I thought maybe if I do a Yes and No Checkbox If Checkbox "Yes?" Is Clicked (CheckBox1) Then Checkbox "No?"(CheckBox2) will be unchecked automatically . I'm Not sure what's the best way to get this done, but I am struggling to figure it out.


If it helps. The scenario is just a Yes Or No Question Answer. So If you click Yes, it opens up a userform, but if you click No a msgbox opens up to tell you to move on to the next question.

Thank you so much for your help!

 

[HackSlash]

The name. The name of your userform where the object exists. You must include the name of the userform to fully qualify the object.

Read this:
https://analysistabs.com/vba-code/excel-userform/optionbutton/

 

[Kyle123]

@HackSlash, I fear you may have gotten the wrong end of the stick. These are forms controls on sheets.

The code you're looking for is:

Public Sub CheckBox1_Click()
    If ActiveSheet.CheckBoxes(Application.Caller) = 1 Then
        UserForm1.Show
    End If
End Sub

 

[rameezl17]

Hi Kyle,

I get an error message saying that "Object doesn't support this property or method."

 

[Kyle123]

Is it definitely a forms control? If expect that with activex

You're not using a Mac are you?

 

[rameezl17]

Yes it is a form control.
No I am not using a Mac.

They prolly with the checkboxes is that I get it to work fine. I just hate the fact that if I try to uncheck the checkbox it causes the userform to open up first (which then you have to close the userform) and then it unchecks the box.

 

[rameezl17]

the problem*

 

[HackSlash]

The answer is in the link I posted. You need to understand how to address your objects.

 

[Kyle123]

@HackSlash no, it's not. The link is for userform controls. These are not userform controls.

 

[Kyle123]

On which line do you get that error? I have this working with the exact code I posted on my screen right now.

 

[rameezl17]

I get an error on the first line after Sub CheckBox1_Click()

Is it because the button is on a excel spreadsheet?