Select Multiple Checkboxes with one checkbox

[mar050703]

Plese forgive if this is not overly helpful, I am new to this forum.

I have a previously written VBA (Excel) code with a userform which has about 15 checkboxes in it. I want to create another checkbox that when checked will automatically check the other 15 (and action the code with in each). I am unabel to find a way to do this.

Each of the 15 checkboxes have names such as Wages, petty cash etc, and are not called checkbox1 etc etc.

Is there a simple bit of code I can write to go with this Select All checkbox (still to be created).

Thanks

 

[Bong]

I really think what you want to do is have a button that selected all 15 of the checkboxes. Let's say you have 3 checkboxes: zaphod, arthur, and marvin, and a button: douglas. The following code would be on the sheet where the controls reside:

Private Sub douglas_Click()
    Sheet1.marvin.Value = True
    Sheet1.zaphod.Value = True
    Sheet1.arthur.Value = True
End Sub

_________________
Bob Rashkin

 

[Fr33dan]

or you could add code to the _Click sub of the checkbox:

Private Sub CheckBox1_Click()
    CheckBox2.Value = CheckBox1.Value
    CheckBox3.Value = CheckBox1.Value
    etc...
End Sub

 

[Andrzejek]

If you have your check boxes on the UserForm, try:

Dim ctl as Control

For each ctl in me.controls
    If TypeOf ctl Is CheckBox Then
        ctl.Value = True
    End If
next ctl

Have fun.

---- Andy

 

[mar050703]

Um Ok, but would is there not a way to loop through, rather than write a bit of code, that entails each of the checkboxes? Would I still use sheet, even though they are checkboxes?

Thanks again

 

[xlbo]

does Andy's code not do that ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Fr33dan]

Andy's got how to loop through them (I didn't know how). Just put that in the _Clicked sub:

Private Sub All_Click()
    Dim ctl As Control
    
    For Each ctl In Me.Controls
        If TypeOf ctl Is CheckBox Then
            ctl.Value = All.Value
        End If
    Next ctl
End Sub

of course all will have to be replaced with whatever the name of the checkbox is that your using. This will decheck the boxes as well.

 

[combo]

For a relatively short list:

Private Sub CheckBox5_Click()
Dim cb As CheckBox
For Each cb In Array(CheckBox1, CheckBox2, CheckBox3, CheckBox4)
    cb.Value = CheckBox5.Value
Next cb
End Sub

combo

 

[mar050703]

The code does not actually check the 15 checkboxes. have I missed something?

 

[Fr33dan]

Try this approach:

Private Sub All_Click()
    Dim ctl As Control
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" Then
            ctl.Value = All.Value
        End If
    Next ctl
End Sub

 

[fumei]

Or if you are having trouble, try fully qualifying:

Sub CheckBox16_Click()
Dim ctl As Control
  For Each ctl In Me.Controls
    If TypeOf ctl Is MSForms.CheckBox Then
       Me.Controls(ctl.Name).Value = CheckBox16.Value
    End If
  Next
End Sub

This makes clicking the 16th checkbox check the 15 other checkboxes.

If you have other checkboxes you do NOT want checked (just specific ones), then you could set up the ones you DO want checked as an array, like this:

Dim CheckEmDano() As Variant
Dim var
CheckEmDano = Array(chkYadda1, chkYadda2, chkYadda3)
For var = 0 To UBound(CheckEmDano)
   CheckEmDano(var).Value = True
Next

This could be used on a _Click event of your SelectAll checkbox, or a commandbutton - it doesn't matter.

In the example there are three checkboxes (chkYadda1...3) out of 15. Only those would be checked.

faq219-2884

Gerry
My paintings and sculpture