Combobox not filtering well
Solved/Closed
Related:
- Combobox not filtering well
- Safesearch filtering is on - Guide
- Content filtering android - Guide
5 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 18, 2010 at 09:55 AM
Jul 18, 2010 at 09:55 AM
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Hi You helped me out a week ago, I developed something a little bit different, but still having problems trying to get two parts of the code working. Honestly I know you gave me not only great help but good ideas I really appreciate that. But Iam asking for your help one more time.
PROBLEM 1
Combobox3 must filter the lanes of the listbox2 if the column D located in data sheet equals 1 for that lane (to see this problem go to the userform in the breakdown tab
PROBLEM 2
when trying to delete more than one record from the tickets sheet is ONLY deleting the last record not the first one, or if you try to delete three records is deleting the last two but not he first one and so on (this poblem is located in
Code in Private Sub CommandButton1_Click())
This is my file https://authentification.site/files/23424547/Lanes2.xls
Thank you rizvisa
PROBLEM 1
Combobox3 must filter the lanes of the listbox2 if the column D located in data sheet equals 1 for that lane (to see this problem go to the userform in the breakdown tab
PROBLEM 2
when trying to delete more than one record from the tickets sheet is ONLY deleting the last record not the first one, or if you try to delete three records is deleting the last two but not he first one and so on (this poblem is located in
Code in Private Sub CommandButton1_Click())
This is my file https://authentification.site/files/23424547/Lanes2.xls
Thank you rizvisa
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 18, 2010 at 07:12 PM
Jul 18, 2010 at 07:12 PM
Could you explain your part 1
for part 2, I am not sure if this is correct.
lDelRow = Sheets(sComboSht).Cells(iListCount, "C")
Sheets(sTicketSht).Rows(lDelRow + 1).Delete
Try this
lDelRow = Sheets(sComboSht).Cells(iListCount +1, "C")
Sheets(sTicketSht).Rows(lDelRow).Delete
for part 2, I am not sure if this is correct.
lDelRow = Sheets(sComboSht).Cells(iListCount, "C")
Sheets(sTicketSht).Rows(lDelRow + 1).Delete
Try this
lDelRow = Sheets(sComboSht).Cells(iListCount +1, "C")
Sheets(sTicketSht).Rows(lDelRow).Delete
Hi thanks for the part#2 is now working, about the part 31 let me rexplain better what is happening:
I have a userform1 with 2 tabs, I am having problems with the second tab called "breakdown" because my combobox3 is populating but it is not filtering the listbox2 by lanes, these lanes are located in the data sheet (column C), also the listbox has to be filtered if criteria in column D also located in the "data" sheet equals "-1" for those lanes.
In other words I want to filter the listbox2 by combobox3 if the column D located in the "data" sheet equals "-1"
Thanks for your time, I will be waiting
Paula
I have a userform1 with 2 tabs, I am having problems with the second tab called "breakdown" because my combobox3 is populating but it is not filtering the listbox2 by lanes, these lanes are located in the data sheet (column C), also the listbox has to be filtered if criteria in column D also located in the "data" sheet equals "-1" for those lanes.
In other words I want to filter the listbox2 by combobox3 if the column D located in the "data" sheet equals "-1"
Thanks for your time, I will be waiting
Paula
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 18, 2010 at 10:06 PM
Jul 18, 2010 at 10:06 PM
Paula I think you are not clear on what the code is doing and/or why
Here are few major parts
1.
Sheet11.Range("A:B").Copy
This copies column A and B. This was true for the previous code as the data that you wanted to show in the listbox(involce and container) were present in these column. Also the drop down was based on invoice which was in Column A as mentioned above.
2,
.Range("A:A").AutoFilter field:=1, Criteria1:="<>" & sCombo1
This filtered the data based on what you did not selected in the drop down. In case of your old code, it would show all invoices but the invoice that you select. Why it was done would be clear by this line
WS.Rows("2:" & lMaxRows).Delete
The delete all the visible row, leaving the header. So as the result the only data left is what you selected.
This all was true for initial problem. Things are different in your new tab. First the the data of dropdown now is in column C ( unlike column A ). Now if you #2 is clear to you would have realized that you need to copy the column C also (along with column A and B as you have it in the listbox). Then you need to apply filter on this column instead of A
Also since you want to additionally where the data is in column D equal to one, you should copy that column too
for filter instead of one column range
have it like
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
and also add to handle your 1
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
If (sCombo1 <> "*") Then
.Range("A:D").AutoFilter field:=4, Criteria1:="=1"
Else
.Range("A:D").AutoFilter field:=4, Criteria1:="<>*"
End If
Here are few major parts
1.
Sheet11.Range("A:B").Copy
This copies column A and B. This was true for the previous code as the data that you wanted to show in the listbox(involce and container) were present in these column. Also the drop down was based on invoice which was in Column A as mentioned above.
2,
.Range("A:A").AutoFilter field:=1, Criteria1:="<>" & sCombo1
This filtered the data based on what you did not selected in the drop down. In case of your old code, it would show all invoices but the invoice that you select. Why it was done would be clear by this line
WS.Rows("2:" & lMaxRows).Delete
The delete all the visible row, leaving the header. So as the result the only data left is what you selected.
This all was true for initial problem. Things are different in your new tab. First the the data of dropdown now is in column C ( unlike column A ). Now if you #2 is clear to you would have realized that you need to copy the column C also (along with column A and B as you have it in the listbox). Then you need to apply filter on this column instead of A
Also since you want to additionally where the data is in column D equal to one, you should copy that column too
for filter instead of one column range
have it like
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
and also add to handle your 1
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
If (sCombo1 <> "*") Then
.Range("A:D").AutoFilter field:=4, Criteria1:="=1"
Else
.Range("A:D").AutoFilter field:=4, Criteria1:="<>*"
End If
Didn't find the answer you are looking for?
Ask a question
Hi, i did the changes and also thanks for takinf the time to explain me, now is working but Im not really sure where i have to put this part of the code:
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
If (sCombo1 <> "*") Then
.Range("A:D").AutoFilter field:=4, Criteria1:="=1"
Else
.Range("A:D").AutoFilter field:=4, Criteria1:="<>*"
End If
into my new code, thanks again, I have tried different ways but is giving me errors
Sheet11.AutoFilterMode = False
Sheet11.Range("A:D").Copy
WS.Range("A1").PasteSpecial
Set rngCell = WS.Range("A:C").Find("*", Cells(1, 1), , , xlByRows, xlPrevious)
If rngCell Is Nothing Then GoTo End_Sub
lMaxRows = rngCell.Row
Set WS = Sheets("Combo2")
With WS
For lRow = 1 To lMaxRows
.Cells(lRow, "D") = lRow
Next lRow
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
lMaxRows = .Cells(Rows.Count, "C").End(xlUp).Row
End With
If (lMaxRows > 1) Then
WS.Rows("2:" & lMaxRows).Delete
End If
WS.AutoFilterMode = False
Set rSource = WS.Cells(2, 3).CurrentRegion
With Me.ListBox2
.ColumnCount = 2
.RowSource = rSource.Address(external:=True)
For i = 1 To rSource.Columns.Count
CW = CW & rSource.Columns(i).Width & ";"
Next i
.ColumnWidths = CW
.ListIndex = -1
.Width = 320
.MultiSelect = fmMultiSelectMulti
End With
End_Sub:
Set rngCell = Nothing
End Sub
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
If (sCombo1 <> "*") Then
.Range("A:D").AutoFilter field:=4, Criteria1:="=1"
Else
.Range("A:D").AutoFilter field:=4, Criteria1:="<>*"
End If
into my new code, thanks again, I have tried different ways but is giving me errors
Sheet11.AutoFilterMode = False
Sheet11.Range("A:D").Copy
WS.Range("A1").PasteSpecial
Set rngCell = WS.Range("A:C").Find("*", Cells(1, 1), , , xlByRows, xlPrevious)
If rngCell Is Nothing Then GoTo End_Sub
lMaxRows = rngCell.Row
Set WS = Sheets("Combo2")
With WS
For lRow = 1 To lMaxRows
.Cells(lRow, "D") = lRow
Next lRow
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
lMaxRows = .Cells(Rows.Count, "C").End(xlUp).Row
End With
If (lMaxRows > 1) Then
WS.Rows("2:" & lMaxRows).Delete
End If
WS.AutoFilterMode = False
Set rSource = WS.Cells(2, 3).CurrentRegion
With Me.ListBox2
.ColumnCount = 2
.RowSource = rSource.Address(external:=True)
For i = 1 To rSource.Columns.Count
CW = CW & rSource.Columns(i).Width & ";"
Next i
.ColumnWidths = CW
.ListIndex = -1
.Width = 320
.MultiSelect = fmMultiSelectMulti
End With
End_Sub:
Set rngCell = Nothing
End Sub
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 19, 2010 at 05:06 AM
Jul 19, 2010 at 05:06 AM
Could you repost you book with your complete newly updated macro
Hi this is the book with the newly updated macro, (is working, the combobox3 is filtering the list box2 but not with the "criteria = 1") without this part of the code
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
If (sCombo1 <> "*") Then
.Range("A:D").AutoFilter field:=4, Criteria1:="=1"
Else
.Range("A:D").AutoFilter field:=4, Criteria1:="<>*"
End If
https://authentification.site/files/23444903/Lanes2.xls
Thanks
Paula
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
If (sCombo1 <> "*") Then
.Range("A:D").AutoFilter field:=4, Criteria1:="=1"
Else
.Range("A:D").AutoFilter field:=4, Criteria1:="<>*"
End If
https://authentification.site/files/23444903/Lanes2.xls
Thanks
Paula
Hi this is the book updated, (is working, the combobox3 is filtering the list box2 but not with the "criteria = 1") without this part of the code
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
If (sCombo1 <> "*") Then
.Range("A:D").AutoFilter field:=4, Criteria1:="=1"
Else
.Range("A:D").AutoFilter field:=4, Criteria1:="<>*"
End If
https://authentification.site/files/23444903/Lanes2.xls
Thanks
Paula
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
If (sCombo1 <> "*") Then
.Range("A:D").AutoFilter field:=4, Criteria1:="=1"
Else
.Range("A:D").AutoFilter field:=4, Criteria1:="<>*"
End If
https://authentification.site/files/23444903/Lanes2.xls
Thanks
Paula
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 20, 2010 at 07:14 AM
Jul 20, 2010 at 07:14 AM
Paula try this
With WS
For lRow = 1 To lMaxRows
.Cells(lRow, "E") = lRow
Next lRow
.Range("A:D").AutoFilter field:=4, Criteria1:="<>1"
lMaxRows = .Cells(Rows.Count, "E").End(xlUp).Row
If (lMaxRows > 1) Then
.Rows("2:" & lMaxRows).Delete
End If
.Range("A:D").AutoFilter field:=4
.Range("A:D").AutoFilter field:=3, Criteria1:="<>" & sCombo1
lMaxRows = .Cells(Rows.Count, "E").End(xlUp).Row
If (lMaxRows > 1) Then
.Rows("2:" & lMaxRows).Delete
End If
End With
