Combobox not filtering well

[Solved/Closed]
Report
-
 MOlie -
Hi everyone,

I really need your help, I was trying by myself to finish my code, but I have more than 5 days trying to figure it out and so far nothing.

My problem is that in my userform1 I have 2 tabs, also the problem is in the breakdown tab my combobox3 is populating but not filtering my listbox2. I want the combobox3 to populate the lanes number if in the data sheet that lane on column "D" is 1, but is not working. Could anybody help me please.

If you need me to post my file I will do it. Thanks I will be waiting






5 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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
By the way i know my real name is irelevant but is Paula
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Hi Risviza, thanks so much, and also thanks for your patience. This problem has been solved.

Paula :)