simple VBA question for excel

I'm a beginning user of visual basic on excel and have a problem i can't quite figure out. i have a user form with a matrix consisting of checkboxes.....about 100 of them. i want to be able to write a bit of code that nests a few loops to check the value of each checkbox. the problem is that i don't want to call out each checkbox by its individual designation ( e.g. checkbox37.value...) but rather in some generic fashion that can be checked in my loop code ( like.....checkbox(count).value). I'm sure there's an easy way to do this but my code writing skills are lacking. thanks for the help...whoiss

You could loop through the Controls collection for the form using TypeOf.

Or if they're all named Checkbox1, Checkbox2, etc. you could do something like:



Dim i As Integer

For i = 1 To 40
Debug.Print Form1.Controls("Checkbox" & i).Value
Next i


I'm pretty sure that'll work for you, but I don't have access to Excel to test it for you right now.

YES!!! that works great. i've been pulling my hair out trying to figure out how to do that. i was going to actually resort to checking each checkbox individually (over 100). my code would have been thicker than "War and Peace". Thank you, whoiss

You could also try something like:

Dim cCont As Control
For Each cCont In UserForm1.Controls
MsgBox cCont.Name & " " & cCont.Value
Next cCont

icjackson, thank you for the code help. i tried this method as well in my application and it works great. i just wish i hadn't waited so long to ask for help. thanks again, whoiss

I need to do the same but I dont have a defined user form, im just using checkbox objects in an excel worksheet. Therefore I can't use form1. etc because form1 doesnt exist. Also, Im using excel 2000 if that matters.
Hope you can help.
Thanks,
Rob

You could loop through the Controls collection for the form using TypeOf.

Or if they're all named Checkbox1, Checkbox2, etc. you could do something like:



Dim i As Integer

For i = 1 To 40
Debug.Print Form1.Controls("Checkbox" & i).Value
Next i


I'm pretty sure that'll work for you, but I don't have access to Excel to test it for you right now.

Use the 'Shapes' collection for the Sheet object. I think you will need to determine the control type another way as well...