Excel Checkbox - Multiple controls-only one may have value of true

Visual Basic for Applications (VBA)

suznal


I have checkboxes on a sheet of which, the value of only one may be true at a time. (By the way, I am not allowed to use option buttons on this workbook). I have figured out the basic code to use if there are only two checkboxes involved, but each instance I have involves a minimum of three and sometimes five checkboxes.

The code I have works well (for a pair of checkboxes) provided I supply it for each control.

  • How can I include multiple checkboxes (only one of which may be True)
  • Do I have to input code for each control, or is there a way around that

Below is the (simple) code I have for one of the checkboxes.

Code Snippet

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

End Sub




Top

Re: Excel Checkbox - Multiple controls-only one may have value of true

Andy Pope


Hi,

This routine placed in a code module.

Public Sub SingleCheckbox(OnSheet As Worksheet, ThisControl As Object)
'
' Uncheck any other checkboxes with the
'
Dim oleTemp As OLEObject

If ThisControl.Object.Value Then
' only deal with control if ticked
For Each oleTemp In OnSheet.OLEObjects
' loop through all controls
If TypeName(oleTemp.Object.Type) = "CheckBox" Then
' only deal with checkboxes
If oleTemp.Object.GroupName = ThisControl.Object.GroupName Then
' make sure control is in same Group
If oleTemp.Name <> ThisControl.Name Then
' do not deal with control just ticked
oleTemp.Object.Value = False
End If
End If
End If
Next
End If

End Sub


And this code in the click event of each control. Modify the 2nd argument to be the correct control.

Private Sub CheckBox1_Click()

SingleCheckbox ActiveSheet, CheckBox1

End Sub






Top

Re: Excel Checkbox - Multiple controls-only one may have value of true

Cringing Dragon

OK, you're not allowed to use option buttons, but would a Combo Box do what you need






Top

Re: Excel Checkbox - Multiple controls-only one may have value of true

Mezhick

I'm sorry for my mistake. Of cause I'm talking about ComboBox in UserForm.



Top

Re: Excel Checkbox - Multiple controls-only one may have value of true

suznal

Andy, with this code would I first have to group the CheckBoxes I would like this applied to first

Would this allow me to have say 5 separate questions on a sheet and only allow one checkbox per question to be marked

If my newbie eyes aren't deceiving me, I would think that this is the case.





Top

Re: Excel Checkbox - Multiple controls-only one may have value of true

Andy Pope

I believe by default the groupname of the controls would be that of the worksheet.
But if you did change the groupname for certain controls the code would indeed only affect those within the same groupname, much like an optionbutton control.




Top

Re: Excel Checkbox - Multiple controls-only one may have value of true

suznal

Dragon, yes a combo box would definately do the trick, unfortunately I can't modify the look of the sheet. It was once a piece of paper that was filled in by hand, but now has joined the computer age. They want it to look exactly the same as we will still be recieving filled in hard copies from some of our vendors who do not have computers.

It's not easy trying to satisfy two different ages-of-man at the same time.





Top

Re: Excel Checkbox - Multiple controls-only one may have value of true

suznal

I have your code in place but when I 'click' on any of the checkboxes I get an error...

"Object does not support this property or method" on the following line...

Code Block
If TypeName(oleTemp.Object.Type) = "CheckBox" Then

Might I have done something wrong





Top

Re: Excel Checkbox - Multiple controls-only one may have value of true

suznal

I know this looks elementary, but below is what I have for a sheet with three checkboxes. It works just as intended, but I couldn't imagine going through all of this for all of the checkboxes included in the workbook. It would really start to look messy too where there are five or six options to choose from.


Code Block

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

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

Private Sub CheckBox3_Click()
If CheckBox3.Value = True Then
CheckBox1.Value = False
CheckBox2.Value = False
End If
End Sub

I like the idea you came up with of a simple line for each control, and the code to loop through and set the values of the checkboxes within groups. Unfortunately I have not advanced that far in my studies yet to understand the "property or method" error.

Please take pity on my ignorance - but I am trying!





Top

Re: Excel Checkbox - Multiple controls-only one may have value of true

MyLady

Hey,

With the code from Andy¡­

if you change this line¡­

If TypeName(oleTemp.Object.Type) = "CheckBox" Then

to¡­

If TypeName(oleTemp.Object) = "CheckBox" Then

then it should work

Best Regards

Cathrine


Top

Re: Excel Checkbox - Multiple controls-only one may have value of true

suznal

MyLady, Thanks!!!

It works fine now.

Andy, I grouped checkboxes together throughout a worksheet and they do in fact work independantly. Thank you!!!





Top