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!

Are these on a worksheet?

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

=countif(a1:a40,TRUE)


tina salgia wrote:

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

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!

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 wrote:

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!

--

Dave Peterson

thanks a lot! that really helped!

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

Hi Tina Salgia,

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

Regards,

---
Orlando Magalhães Filho

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)

"tina salgia" escreveu na mensagem
...
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!