Testing whether any one of five checkboxes are selected

Posted by David Megnin on April 30, 2001 11:25 AM

Hello, I'm trying to write a macro that will test whether any one of five checkboxes are selected.

I've tried this:

Sub SelectCheckBox()
If ActiveSheet.CheckBoxes(6).Value = xlOff And ActiveSheet.CheckBoxes(7).Value = xlOff _
And ActiveSheet.CheckBoxes(8).Value = xlOff And ActiveSheet.CheckBoxes(9).Value = xlOff _
And ActiveSheet.CheckBoxes(10).Value = xlOff Then

MsgBox "Nothing was checked"
Else
MsgBox "At least one box was checked"
End If
End Sub

This only indicates whether checkbox(9) or checkbox(10) were selected.

The indexes and checkbox names may be out of sync.
So I entered "activesheet.checkboxes(6).name" in the immediate pane and got "Run-time error '1004': Unable to get the CheckBoxes property of the Worksheet class"

The spreadsheets are usually protected when I receive them and the checkboxes are from the Forms Menu not the Control Box Menu.

Can anyone help me with this?

Thanks very much!

Posted by Dave Hawley on April 30, 2001 11:48 AM

Hi David

Here are two methods that will get you there!

Sub WhosClicked()
'Written by OzGrid Business Applications
'www.ozgrid.com
'''''''''''''''''''''''''''''''''''''''''''''''''
'Display the name of the Caller which fired the Macro
'''''''''''''''''''''''''''''''''''''''''''''''''
MsgBox Application.Caller
End Sub

Sub AnotherWay()
Dim Cbox As Shape
'Written by OzGrid Business Applications
'www.ozgrid.com
'''''''''''''''''''''''''''''''''''''''''''''''''
'Loops through all CheckBoxes on the ActiveSheet _
and displays the name of any that are Checked.
'''''''''''''''''''''''''''''''''''''''''''''''''
For Each Cbox In ActiveSheet.Shapes
If Cbox.FormControlType = xlCheckBox Then
If ActiveSheet.CheckBoxes(Cbox.Name).Value = 1 Then
MsgBox Cbox.Name
End If
End If
Next
End Sub

Dave

OzGrid Business Applications

Posted by David Megnin on April 30, 2001 12:43 PM

Thanks Dave!

I should have mentioned I'm using Excel 2000.
The first method returned a "type mismatch" error on the MsgBox line.

The second one worked beautifully.

There are several groups of checkboxes on the spreadsheet. I need the macro to indicate if only if none of checkboxes 6,7,8,9 & 10 are selected, then do the same with another group of boxes... 11,12 & 13, say.

Thanks again and sorry for further complicating the scenerio.
-David

Posted by Dave Hawley on April 30, 2001 3:52 PM

David

Give this code a run. Should do the trick.

Sub AnotherWay()
Dim Cbox As Shape
'Written by OzGrid Business Applications
'www.ozgrid.com
'''''''''''''''''''''''''''''''''''''''''''''''''
'Loops through all CheckBoxes on the ActiveSheet _
'and displays the name of any that are Checked.
'''''''''''''''''''''''''''''''''''''''''''''''''
For Each Cbox In ActiveSheet.Shapes
If Cbox.FormControlType = xlCheckBox Then

Select Case Cbox.Name

Case "Check Box 6", "Check Box 7", _
"Check Box 8", "Check Box 9", "Check Box 10"

MsgBox "Group1 = " & Cbox.Name

Case "Check Box 11", "Check Box 12", _
"Check Box 13", "Check Box 14", "Check Box 15"

MsgBox "Group2 = " & Cbox.Name

End Select

End If

Next

End Sub

Dave

OzGrid Business Applications