Checking Checkbox values

[psittacus]

Hi.
I've got a large number (91) of checkboxes and I need to figure out a way to loop through them and check their values. They are control checkboxes, and I've named them using this paradigm: chkTime# (where the # are numbered 1 to 91).
Any help would be great.

[Bob Phillips]

Hi.
I've got a large number (91) of checkboxes and I need to figure out a way to loop through them and check their values. They are control checkboxes, and I've named them using this paradigm: chkTime# (where the # are numbered 1 to 91).
Any help would be great.

Here is some code that shows how to uncheck them all

Sub Uncheck()
Dim oCB As OLEObject 
    With Sheets("Sheet1")
        For Each oCB In .OLEObjects
            If TypeName(oCB.Object) = "CheckBox" Then
                oCB.Object.Value = False
            End If
        Next CB
    End With
End Sub

[EricM]

Are you talking about checking and unchecking them or are you talking about checking the value as in yes it is checked or no it is not checked ?

[Norie]

Why do you have so many?

[EricM]

you could start with something like this
I found it on the web and edited it a bit but it will give you a msg box each time a box is checked

Option Explicit 
 
Sub check_checkbox() 
          Dim obj As OLEObject 
For Each obj In ActiveSheet.OLEObjects 
                If obj.progID = "Forms.CheckBox.1" Then 
                     If obj.Object.Value = True Then 
                          MsgBox obj.Name & " is checked" 
            End If 
        End If 
    Next obj 
End Sub

[psittacus]

Sorry, I should have been more clear. I meant checking their values (Yes or No).

It's really to keep track of which are checked and then store that info in an array.

[Bob Phillips]

Sorry, I should have been more clear. I meant checking their values (Yes or No).

It's really to keep track of which are checked and then store that info in an array.

They don't have a value of Yes or N o, they are either checked, True, or not, False. Both solutions show you how to do it, so try it.

[N_Tonias]

'scan all control in your userform
 For Each Control In frmF1.Controls
If Mid(Control.Name, 1, 3) = "chk" Then
     ' check value of current control
         If Control.Value = True Then 
   Dowhatever
End If
     ' the same thing is to check the corresponding cell
           str1 = Mid(Control.ControlSource, 1, 6)
               If Worksheets("S1").Range(str1) = True Then
   Dowhatever
        End If
       End If
Next Control

[mdmackillop]

Here's an old KB item I never got round to submitting. It may include some code/methods you can make use of.