VBA - On_click event with userform checkoxes

[D4rwin]

Hi,

I'm having some difficulties with some code that I cant quite piece together. I have a Userform which allows a user select a desired parameter from a listbox. Depending on the user's selection a set of checkboxes are created in a neighbouring frame. The checkboxes are iterated and produced in the listbox_click event which works fine. I have also included a SelectAll checkbox which, as the name suggests, iterates through all the checkboxes in the frame and sets each one '.value = true'. However, I would like the 'SelectAll' checkbox to become unchecked as soon as any checkbox within the frame is unchecked.

I have looked around extensively which has lead me to the path of believing the solution lies in creating class modules, collections, and WithEvents. I am however not experienced enough with VBA to fully undestand what's going on in that arena and be able to produce the desired result.

So far, my code looks as follows. Could anyone tell me exactly what I have to do in the class module which is referenced in the red script? (If you are feeling particularly generous perhaps you could also explain what VBA is doing in this situation)

Private Sub ListBox_Click()


    Dim chkBox As Control
    Dim i As Integer
    Dim rng As Range
    Dim ctrl As Control
    
[COLOR=#ff0000]    Dim chkBoxColl As New Collection[/COLOR]
[COLOR=#ff0000]    Dim chkBoxEvent As clsFormEvent[/COLOR]


    Select Case Me.ListBox.Text
        Case "Retailer"
            
            With Frame1
                .Caption = Me.ListBox.Text
                .ScrollHeight = .InsideHeight * (([RetailerCount] * 15 + 30) / .InsideHeight)
            End With
            
            'Remove all pre-existing checkboxes
            For Each ctrl In Frame1.Controls
                If ctrl.Name <> "SelectAllChkBox" Then
                    ctrl.Visible = False
                End If
            Next
            
            'Populate Retailer CheckBoxes with Retailers
            For i = 1 To [RetailerCount]
                Set chkBox = Frame1.Controls.Add("Forms.CheckBox.1", "ChkBox" & i, True)
                chkBox.Caption = WorksheetFunction.Index([LookRetailer], i)
                
[COLOR=#ff0000]                Set chkBoxEvent = New clsFormEvent[/COLOR]
[COLOR=#ff0000]                Set chkBoxEvent.chkBox = Me.Controls("chkBox" & i)[/COLOR]
[COLOR=#ff0000]                chkBoxColl.Add chkBoxEvent[/COLOR]
            Next i

Please help! Any suggestions are welcome. Bear in mind that I am no VBA god, so the simpler the better.
Thanks

 

[Darren Bartrup]

Hi D4rwin,

I had a similar problem a few years back. The code in the link will hopefully point you in the right direction & you're right - class modules are the way to go.

http://www.mrexcel.com/forum/excel-questions/224392-click-event-not-working-solved.html

 

[D4rwin]

Thanks for your response Darren. I looked through your suggestion but it doesn't really help. :/