Collection Control

[crowegreg]

On a form, I have 20 checkboxes for user to select from. I need a command button that will allow the user to click on this button, and all the checkboxes are selected. I've read where I can use collection controls, but I can't figure out the code.

 

[Galaxiom]

There is the VB Collection Object. Each checkbox is added to the collection when the form loads. Then you can refer to the collection. Note the module wide scope of the collection object variable.

Firstly you need to name the checkboxes with a pattern or Tag them so they can be referred to separate from other controls on the form.

In this example I use a Tag of 1.

Option Compare Database
Option Explicit
 
Dim collCheckBox As Collection
 
Private Sub Form_Load()
 
Dim ctrl As Control
 
Set collCheckBox = New Collection
 
   For Each ctrl In Me
        If ctrl.Tag = "1" Then
            collCheckBox.Add ctrl
        End If
   Next
 
End Sub
 
Private Sub MyButton_Click()
 
Dim m As Integer
Dim n As Integer
 
    m = collCheckBox.Count
 
    For n = 1 To m
        collCheckBox.Item(n) = True
    Next
 
End Sub

 

[bob fitz]

Hi

I think the following code will do what you need. It needs to be in the OnClick event of the button.

Dim ctrl As Control
 
For Each ctrl In Me.Controls
If ctrl.ControlType = acCheckBox Then
ctrl.Value = True
End If
Next
[code]
 
You could give users another button to "De-select" all checkboxes by using the following code.
 
[code]
Dim ctrl As Control
 
For Each ctrl In Me.Controls
If ctrl.ControlType = acCheckBox Then
ctrl.Value = False
End If
Next
[code]

 

[Galaxiom]

Bob's code will work too provided all the checkboxes on the form are to be included. You can use the ControlType to select the target checkboxes for the collection too instead of the Tag or Name pattern.

It is not a bad idea to use collections particularly if there are many different groups of controls on a form because it simplifies the code in the procedures. Of course it simply transfer the complexity to the Form_Load procedure.

It will also save a microsecond or two each time the button is clicked because it is faster to address the collection than loop through every control and determine if it should be affected.

Regarding the UnCheck I would normally use a single procedure for both Check and UnCheck.

Private Sub btnCheckTrue_Click
 
   CheckBoxesSet(True)
 
End Sub
 
Private Sub btnCheckFalse_Click
 
   CheckBoxesSet(False)
 
End Sub
 
 
Private Sub CheckboxesSet(Checked as Boolean)
 
Dim m As Integer
Dim n As Integer
 
   m = collCheckBox.Count
 
   For n = 1 To m
      collCheckBox.Item(n) = Checked
   Next
 
End Sub

 

[stopher]

Private Sub CheckboxesSet(Checked as Boolean)
 
Dim m As Integer
Dim n As Integer
 
   m = collCheckBox.Count
 
   For n = 1 To m
      collCheckBox.Item(n) = Checked
   Next
 
End Sub

Galaxiom, I like your idea of using a collection.

Of course, the code above could be written:

Dim chkBox As CheckBox
 
   For Each chkBox In collCheckBox
      chkBox = Checked
   Next

Interestingly, we could put all our check boxes in a sub form and then just iterate the entire collection. This negates the need for any On Load code. So the code would be:

Private Sub CheckboxesSet(Checked As Boolean)
Dim ctrl As Control
For Each ctrl In Me.mySubForm.Form
    ctrl = Checked
Next
 
End Sub

Chris

 

[Galaxiom]

Galaxiom, I like your idea of using a collection.

I only posted the collection solution because that is what the OP asked about.

 

[stopher]

Interestingly, we could put all our check boxes in a sub form and then just iterate the entire collection. This negates the need for any On Load code. So the code would be:

Private Sub CheckboxesSet(Checked As Boolean)
Dim ctrl As Control
For Each ctrl In Me.mySubForm.Form
    ctrl = Checked
Next
 
End Sub

Chris

Er maybe this isn't so good. It two forms trying to edit the same record at the same time . Is there a way round this?

 

[stopher]

Er maybe this isn't so good. It two forms trying to edit the same record at the same time . Is there a way round this?

I think I've found a solution...

The tab control can be used as a container. Setting the Style of the tab control to None removes the tabs. And then setting the back style to transparent makes the control invisible.

So the code is:

Dim ctrl As Control
For Each ctrl In Me.myTabPage.Controls
ctrl = Checked
Next

This seems to create the effect of layering (like in photo-editing). So I guess we could have several layers, each with it's own code.

No more tagging

Haven't tested extensively though....

 

[crowegreg]

Thank you everyone for providing a solution. Based on my form, I decided to use Bob's code. It worked perfectly!!

 

[crowegreg]

What code should I use to confirm that at least 1 check box is selected?

 

[bob fitz]

Hi
You could use a function that returns a True/False value to confirm that at least one checkbox has been ticked. This should work for you:

Public Function fnOneOrMoreChecked() As Boolean
On Error GoTo Err_fnOneOrMoreChecked_Error
    Dim ctrl As Control
    
    For Each ctrl In Me.Controls
        If ctrl.ControlType = acCheckBox Then
            If ctrl.Value = True Then
                fnOneOrMoreChecked = True
                GoTo Exit_ErrorHandler
            End If
        End If
    Next

Exit_ErrorHandler:
    Exit Function

Err_fnOneOrMoreChecked_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fnOneOrMoreChecked "
    Resume Exit_ErrorHandler

End Function
[code]

 

[Galaxiom]

I think I've found a solution...

The tab control can be used as a container. Setting the Style of the tab control to None removes the tabs. And then setting the back style to transparent makes the control invisible.

Going to have to try this one. Innovative as always Chris.

 

[crowegreg]

Thanks Bob, that's exactly what I needed.