Excel Checkbox - Multiple controls-only one may have value of true
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
[979 byte] By [
suznal] at [2008-1-10]
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
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.
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.
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.
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?
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!
