Userform Checkboxes

[jamescooper]

So my following code successfully only allows either checkbox 1 or checkbox 2 to be ticked at one time.

I want to now expand this with Checkbox 8 OR checkbox 9.

I need to know how to adapt the code below to achieve this please?

Thanks.

Private Sub CheckBox1_Change()

Dim ctl As MSForms.Control

If Me.CheckBox1.Value = True Then
 
 For Each ctl In Me.Controls
 If TypeName(ctl) = "CheckBox" And ctl.Name <> "CheckBox1" Then
 ctl.Value = False
 End If
 Next ctl
 End If

End Sub
 
Private Sub CheckBox2_Change()

Dim ctl As MSForms.Control

If Me.CheckBox2.Value = True Then
 
 For Each ctl In Me.Controls
 If TypeName(ctl) = "CheckBox" And ctl.Name <> "CheckBox2" Then
 ctl.Value = False
 End If
 Next ctl
 End If

End Sub

 

[Sunjinsak]

Is there a reason you have to use check boxes? If it's an either/or option you should probably use groups of option buttons instead.

 

[jamescooper]

Just a preference, I think this code is OK, just needs adapting?!

 

[Sunjinsak]

You can definitely adapt the code but honestly option buttons are your better bet. No code necessary. Option buttons are designed specifically for exactly this situation - when you want one option OR another. You can group option buttons together using, say, a frame control for example then at run time you will only be able to select one option within each group at a time. You can have as many option buttons in each group as you like - it isn't restricted to just two.

Still, if you really want to use check boxes instead I can help you adapt your code but I'll have to post back later as I'm in work at the moment and am about to go to a meeting. Hopefully someone else will post a response in the meantime so you're not waiting around but if not I'll be back later...

 

[jamescooper]

Thank you for the feedback and guidance - I will use option buttons instead.

 

[Sunjinsak]

You're welcome.

The following link might help you choose when you'd want to use a CheckBox control versus an OptionButton control...

Option Buttons and Checkboxes UserForm Controls Part 1. Excel Training VBA 2 lesson 17

Option Buttons and Checkboxes UserForm Controls Part 1. Excel Training VBA 2 lesson 17

www.ozgrid.com