6
votes

I'm trying to 'reset' a listbox in Excel VBA when a form closes. Currently when I use the userform1.hide function the form disappears but when I open it up again using the .show function it still has the previous selections in it. As someone who is relatively new to this can anyone help?

The code for the listboxes is as follows:

Sub CommandButton1_Click()

'Filter by Country
Dim item As Long, dict As Object
Dim wsData As Worksheet

Set wsData = Sheets("TPID")
Set dict = CreateObject("Scripting.Dictionary")

With ListBox1
    For item = 0 To .ListCount - 1
        If .Selected(item) Then dict(.List(item)) = Empty
    Next item
End With

With wsData.ListObjects("Table_ExternalData_1").Range
    .AutoFilter Field:=1
    If dict.Count Then _
        .AutoFilter Field:=1, criteria1:=dict.keys, Operator:=xlFilterValues
End With
'Filter by Continent
Dim item1 As Long, dict1 As Object
Dim wsData1 As Worksheet

Set wsData1 = Sheets("TPID")
Set dict1 = CreateObject("Scripting.Dictionary")

With ListBox2
    For item1 = 0 To .ListCount - 1
        If .Selected(item1) Then dict1(.List(item1)) = Empty
    Next item1
End With

With wsData1.ListObjects("Table_ExternalData_1").Range
    .AutoFilter Field:=4
    If dict1.Count Then _
        .AutoFilter Field:=4, criteria1:=dict1.keys, Operator:=xlFilterValues
End With


End Sub

Thanks in advance everyone,

6

6 Answers

5
votes

If you want to clear ONLY the selection (as you are using hide, not unload) then use:

me.listbox1.value = ""

If it is a multiselect listbox, you need to use:

Me.listbox1.MultiSelect = fmMultiSelectSingle
Me.listbox1.Value = ""
Me.listbox1.MultiSelect = fmMultiSelectMulti

this will clear the selection by setting it to single selection only and then clearing the selection, then setting the functionality to multi select again.

If you want to clear the entire list box (the options that you select) use:

Me.listbox1.clear
1
votes

try this code to Clear listbox in VBA

Private Sub clearListBox()
    Dim iCount As Integer

    For iCount = 0 To Me!ListBox1.ListCount
        Me!ListBox1.Selected(iCount) = False
    Next iCount
End Sub
1
votes

To reset the apparent item selected in the listbox, try:

ListBox1.ListIndex = -1
ListBox2.ListIndex = -1

There will be no apparent item in the listbox control after that.

1
votes

You could use

Private Sub clearListBox()
 'Clears the listbox
    Do Until ListBox1.ListCount = 0
        Me!ListBox1.RemoveItem(0)
    Loop
End Sub
0
votes

Hide and show has no effect. If you want to use "brute force", use unload then load, but it will reset everything (not just the radio buttons) and will be memory consuming (well, if your form doesn't contain thousands of components and your computer is recent etc etc it will be fine though)

another way to do what you want is simply to run through all radio buttons and uncheck them all

0
votes

Adapting LBPLC's clever technique, here's a single solution that works in all cases:

Sub ListBox1_Reset()
    Dim SaveMultSelectMode As Integer
    
    With Me.ListBox1
        SaveMultSelectMode = .MultiSelect
        .MultiSelect = fmMultiSelectSingle
        .Value = ""
        .MultiSelect = SaveMultSelectMode
    End With
End Sub