unchecking the checkbox in userform

Question

I have an userform designed with 3 Listboxes which are multiple option.

Also, I have three commandbuttons, which are "Filter", "Unfilter" and "Exit".

I have the access to the userform by the button in the sheet, with the below code.

sub dev()
userform1.show
end sub

From the userform, I select the checkboxes and then click "Filter". With this I could see the filtered result in my sheet "Data".

the problem is when I am clicking the button from the sheet "Data" the listbox has the previous selection. I would like to clear the previous selection In the list box. Currently, I have this clear case in my unfilter button.

I would like to have it in the main command button.

I tried something like this

Sub dev()
Userform1.show
Call userform1.CBUnfilter
end sub()

but this showed the "invalid use of property error".

Can anyone help me how I can do this ?

this is the code for my filter button in the userform

Private Sub CBFilter_Click()
   If CBFilter.Caption = "Filter" Then
   Call DoFilter.DoFilter
   Else
   'do something
End If
End Sub

this is the code for the unfilter button in the userform

Private Sub CBUnfilter_Click()
Dim icount, jcount, kcount As Integer
If CBUnfilter.Caption = "Unfilter" Then

For icount = 0 To Me!ListBox1.ListCount
Me!ListBox1.Selected(icount) = False
Next icount

For jcount = 0 To Me!ListBox2.ListCount
Me!ListBox2.Selected(jcount) = False
Next jcount

For kcount = 0 To Me!ListBox3.ListCount
Me!ListBox3.Selected(kcount) = False
Next kcount
End If
End Sub

Could someone help to have the unfilter function that can be called from the button in the sheet.

Answer 1

Private Sub CBUnfilter_Click()
Dim icount, jcount, kcount As Integer

For icount = 0 To Me!ListBox1.ListCount -1
Me!ListBox1.Selected(icount) = False
Next icount

For jcount = 0 To Me!ListBox2.ListCount-1
Me!ListBox2.Selected(jcount) = False
Next jcount

For kcount = 0 To Me!ListBox3.ListCount-1
Me!ListBox3.Selected(kcount) = False
Next kcount
End Sub

And CBUnfilter is the button name,right? Not a module Sub to call

Call userform1.CBUnfilter

So the same way, the filter button is:

Private Sub CBFilter_Click()
Dim icount, jcount, kcount As Integer

For icount = 0 To Me!ListBox1.ListCount-1
Me!ListBox1.Selected(icount) = True
Next icount

For jcount = 0 To Me!ListBox2.ListCount-1
Me!ListBox2.Selected(jcount) = True
Next jcount

For kcount = 0 To Me!ListBox3.ListCount-1
Me!ListBox3.Selected(kcount) = True
Next kcount
End Sub

And to Exit correctly:

Private Sub Exit_Click()

'Your exit code here
 Me.Hide
  Unload Me
End Sub

Or:

Private Sub Exit_Click()

'Your exit code here
 Userform1.Hide
  Unload Userform1
End Sub

Answer 2

Userform1.show(0) userform show as modaless

Sub dev()
UserForm1.Show (0) 

ResetListbox UserForm1.ListBox1
ResetListbox UserForm1.ListBox2
ResetListbox UserForm1.ListBox3

End Sub
Sub ResetListbox(lsbox As Object)
    With lsbox
        For i = 0 To lsbox.ListCount
             .Selected(i) = False
        Next i
    End With
End Sub

Answer 3

The answer entirely depends on how you 'remove' your UserForm.

If you Unload the form programmatically (ie Unload UserForm1) or the user closes the UserForm manually (ie by clicking the red cross), then all current settings will be lost. You haven't said how you've populated the items in your listboxes so I've assumed you've done it programmatically. If that's the case, all your listboxes will be cleared - not just selections, but items too.

If you Hide the form, which can only be done programmatically (ie Me.Hide), then all current settings will remain and, when you show the UserForm again those settings will be visible.

I have made a best guess at mimicking your structure, ie: you have a Worksheet called "Data" onto which you have inserted a CommandButton. The macro you have assigned to this CommandButton is called 'dev' and the 'dev' code is in Module1.

The Unload Method

For this case I've assumed that the "Exit" command button contains the following code:

Private Sub CBExit_Click()
    Unload Me
End Sub

You could populate your listboxes in the UserForm_Initialize() event. (You can access the routine for this event by clicking on 'UserForm1`, selecting 'Useform' in the first combobox below the menu bar and then 'Initialize' in the second.) I've put some sample arrays in the code behind, but you'd populate it as you wish:

Private Sub UserForm_Initialize()
    Dim arr(2) As Variant

    arr(0) = Array("A", "B", "C", "D", "E", "F", "G", "H")
    arr(1) = Array("A", "B", "C", "D", "E", "F")
    arr(2) = Array("A", "B", "C", "D")

    Me.ListBox1.List = arr(0)
    Me.ListBox2.List = arr(1)
    Me.ListBox3.List = arr(2)
End Sub

Your 'dev' routine would remain as is and each time the useform becomes visible, the listboxes will be cleared.

The Hide Method

For this case I've assumed that your "Exit" button code is:

Private Sub CBExit_Click()
    Me.Hide
End Sub

In your userform code, you'd have a couple of routines, a public one which can be called from a Module and a private one to clear the boxes:

Public Sub Reset()
    ClearListbox Me.ListBox1, Me.ListBox2, Me.ListBox3
End Sub

Private Sub ClearListbox(ParamArray lboxes() As Variant)
    Dim item As Variant
    Dim lbox As MSForms.ListBox
    Dim i As Long

    For Each item In lboxes
        Set lbox = item
        For i = 0 To lbox.ListCount
            lbox.Selected(i) = False
        Next
    Next
End Sub

Then your 'dev' code would simply be:

Public Sub dev()
    With UserForm1
        .Reset
        .Show
    End With
End Sub

Incidentally, your 'Unfilter' button could also call the ClearListBox routine:

Private Sub CBUnfilter_Click()
    ClearListbox Me.ListBox1, Me.ListBox2, Me.ListBox3
End Sub