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:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,782
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,088
Messages
5,835,314
Members
430,351
Latest member
ddalton

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