Deselect an item from a listbox in a userform.

Juanca1080

New Member
Joined
May 28, 2015
Messages
17
Hello,
I’ve been having problems with a simple listbox which I just can’t get to work, so I hope someone could help me here.
I’m working on a userform which is intended to display the content of the records in a worksheet in a set of textboxes.
The thing is that I want letting the user to select the records that are displayed, based on a field in the table. So I added a listbox with three items (all, pendent or answered), so that when the user changes the selected item the table is filtered automatically based on that criteria. Everything works great, except when the filter is applied and no coincidences are found.
For example, if the user selects to display only pendent claims, but there are no pendent claims on the table, I want to display a message and then proceed to select the first item of the listbox, but it has been impossible to deselect the “pendent” item. In fact, executing it step by step, I found that it does select the first item but then goes back to the second.
Nothing seems to work and I know there are other ways to accomplish what I want, like using option buttons or using a command button to apply the filters, but I really want to learn and that’s why I decided to post my question.

So here’s the code, please note that I have to use a variable to disable events, to avoid showing the message twice, since it selects the pendent item again as I described.

Code:
Private Sub LbxFilter_Change()
If EnableEvents = True Then
Select Case LbxFilter.ListIndex
Case 0 'Show all 
    Sheets(TbProcess.Value).ListObjects(1).DataBodyRange.AutoFilter field:=8
    Call ImportArray
    Call PopulateRows
    Call FillFields(0)
Case 1 'Show pendent only
    Sheets(TbProcess.Value).ListObjects(1).DataBodyRange.AutoFilter field:=8
    Sheets(TbProcess.Value).ListObjects(1).DataBodyRange.AutoFilter field:=8, Criteria1:=vbNullString
    NoRecords = Application.WorksheetFunction.Subtotal(103, Sheets(TbProcess.Value).ListObjects(1).ListColumns(1).DataBodyRange) - 1
    Select Case NoRecords
    Case Is >= 0
        ReDim Rows(NoRecords)
        Call PopulateRows
        Call FillFields(0)
   [B] Case Else	‘HERE’S THE ISSUE
        MsgBox "No pendent claims found.", vbExclamation
        LbxFilter.ListIndex = 0
        EnableEvents = False[/B]    
    End Select 
Case 2 'Show answered only
    Sheets(TbProcess.Value).ListObjects(1).DataBodyRange.AutoFilter field:=8
    Sheets(TbProcess.Value).ListObjects(1).DataBodyRange.AutoFilter field:=8, Criteria1:="<>"
    NoRecords = Application.WorksheetFunction.Subtotal(103, Sheets(TbProcess.Value).ListObjects(1).ListColumns(1).DataBodyRange) - 1
    Select Case NoRecords 
    Case Is >= 0
        ReDim Rows(NoRecords)
        Call PopulateRows
        Call FillFields(0)
    Case Else
        MsgBox "No answered claims found.", vbExclamation
        LbxFilter.ListIndex = 0
        EnableEvents = False
    End Select
End Select
End If
End Sub
 
Last edited:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Your variable currently isn't achieving anything. You need a line like:
Code:
If EnableEvents = False Then Exit Sub
at the top of the Change event.
Then, before you change the listindex, set Enableevents to False, then reset it to True after you are done.
 

Juanca1080

New Member
Joined
May 28, 2015
Messages
17
Hi RoryA, thanks a lot for the response.
I tried it and it certainly helps to prevent the message from displaying twice. Nevertheless, the other part of the problem is still there. Despite of the instruction LbxFilter.Listindex = 0, at the end of the procedure the “focus” remains in the second item.
I also tried other options as below, with no success either.

Code:
LbxFilter.ListIndex = -1

For i = 0 to LbxFilter.ListCount –1
LbxFilter.Selected(i) = False
Next i

Maybe I should use a command button to apply the filter and deselect all items instead. Although I can’t help wondering about the cause of that unusual behavior.
Thanks again for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,322
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top