how to determine which checkboxes are checked

[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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Dave Peterson]

Are these on a worksheet?

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

=countif(a1:a40,TRUE)

 

[tina salgia]

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Orlando Magalhães Filho]

Hi Tina Salgia,

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

Regards,

 

[Dave Peterson]

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.

 

[tina salgia]

thanks a lot! that really helped!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!