CODEWITHSUNDEEP
vbaexcellistbox
user2753700
user2753700

Reputation: 71

Reset listbox selection in VBA

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,

Upvotes: 7

Views: 77858

Answers (7)

Aranxo
Aranxo

Reputation: 1183

In MS Access this one works for a multiselect Listbox:

Dim Item As Variant

For Each Item In Listbox1.ItemsSelected
    Listbox1.Selected(Item) = False
Next Item

Upvotes: 0

pstraton
pstraton

Reputation: 1120

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

But note that this doesn't work if called from within the ListBox control's VBA Change event. Probably, that's due to Excel's ListBox change-event code freezing the effective state of the MultiSelect property during processing, in order to prevent unstable conditions for its own processing.

Upvotes: 0

BenJr
BenJr

Reputation: 11

You could use 

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

Upvotes: 1

InHoaxidable
InHoaxidable

Reputation: 31

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.

Upvotes: 1

user28864
user28864

Reputation: 3453

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

Upvotes: 1

LBPLC
LBPLC

Reputation: 1571

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

Upvotes: 7

Thomas
Thomas

Reputation: 8930

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

Upvotes: 0

Related Questions