how to determine which checkboxes are checked

  • Thread starter Thread starter tina salgia
  • Start date Start date
T

tina salgia

Hi,
I have 40 check boxes in an application. I want to determine
programatically, which checkboxes the user has ticked (or checked).
based on the checkbox ticked i want to run some code. is there some way
in excel to comprehensively determine this? currently i am using if-then
condition 40 times! the names of my checkboxes are very intuitive :
checkbox1, checkbox2, checkbox3...

thanks in advance,
tina
 
Are these on a worksheet?

If you used a linked cell (in a nice range), you could do:

=countif(a1:a40,TRUE)
 
Hi Dave,
The buttons reside on a worksheet. I havent linked these buttons to any
cell. they are standalone (i dont know if this is the correct term).
so i cannot use the method that you described. but if you can tell me
how to link them to cells, then it would probably make my life easier.
also if you cud suggest something with my current setting, it would be
great!
Thanks
Tina
 
Hi Tina Salgia,

Which textbox tool are you using? From Form toolbar or from Control Toolbox
bar?

Regards,
 
What kind of checkboxes are they? Did you get them from the Forms toolbar or
from the ControlToolbox toolbar?

The first uses the Forms toolbar. The second from the ControlToolbox toolbar.


Option Explicit
Sub testme01()

Dim myCBX As CheckBox
Dim iCtr As Long

iCtr = 0
For Each myCBX In ActiveSheet.CheckBoxes
If myCBX.Value = xlOn Then
iCtr = iCtr + 1
End If
Next myCBX

MsgBox iCtr

End Sub

Sub testme02()

Dim OLEObj As OLEObject
Dim iCtr As Long

iCtr = 0
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
If OLEObj.Object.Value = True Then
iCtr = iCtr + 1
End If
End If
Next OLEObj

MsgBox iCtr

End Sub

You can just rightclick on the forms toolbar checkboxes, choose format control,
then Control Tab to get to the cell link.

For the controltoolbox checkboxes. Show that toolbar. Click on design mode.
Right click on the checkbox and select properties. Look for linkedcell.

If I was only looking to add them up, I wouldn't bother linking them. But if I
wanted to examine each (one by one), I might.
 
Back
Top