Solved: Select All and Unselect all

[Djblois]

I am using code I got out of a book to select all checkboxes or uncheck all checkboxes depending on what the user clicks. Unfortunatley, I can't get it to work:

Class Module: clsFormEvents

[vba]Option Explicit
Public WithEvents chb As MSForms.CheckBox
Public Sub SelectAll()
chb.Value = True
End Sub
Public Sub UnselectAll()
chb.Value = False
End Sub[/vba]

Code Behind the Form: ShowColumns

[vba]Private Sub ShowColumns_Initialize()
Dim ctl As MSForms.CheckBox
Dim chb_ctl As clsFormEvents
'Go through the checkboxes and add them to the frame
For Each ctl In f_Columns2.Controls
Set chb_ctl = New clsFormEvents
Set chb_ctl.chb = ctl
col_Selection.Add chb_ctl
Next ctl
End Sub[/vba]

Code behind the Select all label:

[vba]Private Sub SelectColumns2_Click()
Dim ctl As clsFormEvents
For Each ctl In col_Selection
ctl.SelectAll
Next ctl
End Sub
[/vba]

Code behind the unSelect all label:

[vba]Private Sub unselectcolumns2_Click()
Dim ctl As clsFormEvents
For Each ctl In col_Selection
ctl.UnselectAll
Next ctl
End Sub[/vba]

I am not getting an error or anything, it just doesn't work when I click on either of the labels.

[Bob Phillips]

There is a load wrong wioth this code as presented.

- col_Selection isn't defined, I guess it is a collection of clsFormEvents

- what is f_Columns2.Controls?

- you don't specify the form name in the form initialize event, you use the generic Userform_Initialize

This userform code works

[vba]

Option Explicit

Dim col_Selection As Collection

Private Sub SelectColumns2_Click()
Dim ctl As clsFormEvents
For Each ctl In col_Selection
ctl.SelectAll
Next ctl
End Sub

Private Sub unselectcolumns2_Click()
Dim ctl As clsFormEvents
For Each ctl In col_Selection
ctl.UnselectAll
Next ctl
End Sub

Private Sub UserForm_Initialize()
Dim ctl As Control
Dim chb_ctl As clsFormEvents
'Go through the checkboxes and add them to the frame
Set col_Selection = New Collection
For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then
Set chb_ctl = New clsFormEvents
Set chb_ctl.chb = ctl
col_Selection.Add chb_ctl
End If
Next ctl
End Sub

[/vba]

[Djblois]

When I don't use the form name I get a type mismatch error when I try to load the form

[Djblois]

Xld,

Thank you,
Your code works

[Djblois]

I understand how it works basically but how do I get it to work with 2 different collections of checkboxes on one form. Of course they will have seperate buttons and they are both inside seperate frames.

[Djblois]

Xld,

Thank you for all the help, I got it to work!!!!!!

[Bob Phillips]

Can you share what you did for anyone who might look at this thread?

[Djblois]

Create 2 seperate frames with checkboxes. col_Selection would be the collection for one of the frames and col_Selection2 would be the code for the second frame. The Select all and Unselect all would be put behind the controls you are using.

[VBA]Option Explicit
Dim col_Selection As New Collection
Dim col_Selection2 As New Collection

Private Sub CommandButton1_Click()
Organize.Hide
End Sub

Private Sub SelectColumns_Click()
Dim ctl As clsFormEvents
For Each ctl In col_Selection
ctl.selectall
Next ctl
End Sub
Private Sub selectInvoices_Click()
Dim ctl As clsFormEvents
For Each ctl In col_Selection2
ctl.selectall
Next ctl
End Sub
Private Sub unSelectColumns_Click()
Dim ctl As clsFormEvents
For Each ctl In col_Selection
ctl.unselectall
Next ctl
End Sub
Private Sub unselectinvoices_Click()
Dim ctl As clsFormEvents
For Each ctl In col_Selection2
ctl.unselectall
Next ctl
End Sub
Private Sub UserForm_Initialize()
Dim ctl As Control
Dim chb_ctl As clsFormEvents
'Go through the checkboxes and add them to the frame
Set col_Selection = New Collection
For Each ctl In Columns.Controls
If TypeName(ctl) = "CheckBox" Then
Set chb_ctl = New clsFormEvents
Set chb_ctl.chb = ctl
col_Selection.Add chb_ctl
End If
Next ctl

Set col_Selection2 = New Collection
For Each ctl In Invoices.Controls
If TypeName(ctl) = "CheckBox" Then
Set chb_ctl = New clsFormEvents
Set chb_ctl.chb = ctl
col_Selection2.Add chb_ctl
End If
Next ctl

End Sub[/VBA]

[Bob Phillips]

Good. Thanks