Reading a Check Box status?

[bigmc6000]

I have a bunch of options for someone to select (about 45) and they want to be able to check the ones they want. Press an "update" button, go the next page and show only those that they selected. Now - I named all my CB's (Check boxes) CBo1...CBo45 in hopes to be able to reference them. Is there any way to read the current status of a box (ie checked vs not checked) using the references CBo1 - CBo45?

I've tried reffering to the box using an If command but naming the CB in the name field in the upper left doesn't allow excel to figure out what the heck it's doing. So I'm looking for a way in VB to have some type of a for loop from 1 to 45 - check all the boxes. Make a list of those boxes that have been checked and make them all viewable on teh next sheet. Hopefully this isn't too confusing!


Thanks in advance!!!

 

[Tazguy37]

How about something like this?:

Dim cb As Object

    For Each cb In Worksheets("Sheet1").CheckBoxes
        Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) = cb.Name
        If cb.Value = xlOn Then
            Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1) = "Checked"
        Else
            Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1) = "Unchecked"
        End If
    Next

Hope that helps!

 

[bigmc6000]

Minor problem...

At the first "worksheets("Sheet2")..." I get an error "Subscript out of range." Any ideas about that?

Is there a way to look up the name of the cb then paste "checked" or "unchecked" beside it? Like a VLookup function for VB?

Thanks

 

[bigmc6000]

It dimensions "cb" as -4146 is that helps fix some issues - I really have no idea why it does that thou...

 

[Tazguy37]

Re: Minor problem...

At the first "worksheets("Sheet2")..." I get an error "Subscript out of range." Any ideas about that?

Is there a way to look up the name of the cb then paste "checked" or "unchecked" beside it? Like a VLookup function for VB?

Thanks

You'll have to change "Sheet2" in the code to the name of your sheet where you want the results.

Also, -4146 is "xlOff"; that means that checkbox is not checked.

 

[bigmc6000]

wow - nevermind. I had "Sheet2" rather than "Sheet 2."

Works great now - Thanks!!!!!

 

[bigmc6000]

I more question actually. I've named a bunch of boxes in groups. I.E. Group 1, group 2, etc... How do I make a code such that on a button press it will select only those in group one. I'm having some difficulty selecting my group and having look at each checkbox in the group.


Thanks

 

[Tazguy37]

Can only one selection be made per group? If so, then you might consider going with option buttons instead of checkboxes.

 

[bigmc6000]

Well - I'd like to just say for all cb's in "group 1" because I went to a lot of pain in naming all of those cb's (and my boss wants cbs...)

Just some syntax that'll recognize all the objects in "group 1" and turn them "on" is really what I'm looking for. I'm having some issues tho as the the VB help is all but worthless...

Thanks

 

[bigmc6000]

Ok well I fixed my problem simply by setting a for loop just to scan a given section depening upon which button they click (ie. for 1 to 20 for group 1, 21 to 40 for group 2, 41-60 for group 3)