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.
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: