Show unique list in listbox

[Solved/Closed]
Report
-
 MOlie -
Hello,

Going trought some research I found some answers. Now my actual problem is how to sort the values in listbox1 by the ones showed in combobox1. example

Combobox2

Tim 32
Laura 28
Alex 10

Listbox1

Tim 32
Laura 28
Alex 23
Laura 27
Tim 29
Mario 34

So If I select Tim in the combobox2 the listbox1 would will show up only "Tim 32". Can anyone help me with a code for that.

Thanks Guys



5 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
First, this is just a suggestion, try to have your example inline with the data. It is easy for you to explain and other to understand. Here you are talking about Tom. Laura, Alex etc. and in the book it is A, B C. It just creates confusion or may be it is just me.

Any how, on your issue. It seems that you invoice list is connected to Invoice sheet , column A and B. The most clean way that I can think of is that have the invoice data from column A and B copied over to a new location based on what you have selected in the combo box and have the list box, used data from that copied data. Any issues ?
Hi, The only issue is that I do not know how to do it, umm if somebody can help me with the code that would be awesome.

I also apologize for mixing my ideas, I was trying only to give an easy way to show my problem.

Molie
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Do this

1. Add a new sheet and name it Combo
2. Modify your routine "UserForm_Initialize" as
Private Sub UserForm_Initialize() 

    Call populateComboBox("*") 
     
End Sub


3. Add this code to userform


Private Sub ComboBox2_Change() 

    Call populateComboBox(ComboBox2.Value) 
     
End Sub 

Sub populateComboBox(sCombo As String) 
Dim Cell As Range 
Dim rSource As Range 
Dim CW As String 
Dim WS As Worksheet 

    Sheets("Combo").Cells.Clear 
     
    Sheet9.AutoFilterMode = False 
    Set Cell = Sheet9.Range("A:B").Find("*", Cells(1, 1), , , xlByRows, xlPrevious) 
     
    If Cell Is Nothing Then Exit Sub 
    Set Cell = Nothing
     
    Sheet9.AutoFilterMode = False 
    Sheet9.Range("a:a").AutoFilter field:=1, Criteria1:="=" & sCombo 
    Sheet9.Range("a:b").Copy 
    Sheets("Combo").Range("a1").PasteSpecial 
     
    Set WS = Sheets("Combo") 
    Set rSource = WS.Cells(2, 3).CurrentRegion 
    With Me.ListBox1 
         
        .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 
Hi thats great and more than great.

Well you have done a lot for me, I do not want to bother you any more, but doyou have an example of how can I do this: "Iam not really sure how to transfer the records selected from the listbox1 trought the combobox1 to a specific sheet"

Thanks a lot rizvisa
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
sorry i was not able to understand. could u explain
There is any way I can use the records selected in my listbox1 and use a different dropdown menu, in this case in my file, combobox1 (this combobox has the name of some of the sheets in my file) to transfer the values selected into the sheet I select trought my combobox. example

Let's say that I select the next records from the listbox1:

A FOLOLL2312
A FOLOLL2313

Once they are selected using combobox1 (which has some of the sheet names of my file) transfer those records below the headers in that page. If I select from the combobox1 Lane G1, then transfer those records to that page. Sometimes it could be two records, other times it could be 10 or 20 records or more...

Umm I hope this time is more clear.. Once again ,Thank you
I had this idea, but Iam having one problem, the code that iam using is only trasnfering the first row from the multiselect listbox and i need the two columns in the listbox1 pasted into the right sheets. This is the code Iam using now:

Private Sub CommandButton1_Click()

If ComboBox1.Value = "LANE 1" Then

Dim iListCount As Integer
Dim iRow As Integer
Dim rStartCell As Range

Set rStartCell = Sheet1.Range("A655").End(xlUp)

For iListCount = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(iListCount) = True Then
ListBox1.Selected(iListCount) = False
iRow = iRow + 1
rStartCell.Offset(iRow).Value = ListBox1.List(iListCount, 0)
End If
Next iListCount
Set rStartCell = Nothing

ElseIf ComboBox1.Value = "LANE 2" Then

Dim iListCount2 As Integer
Dim iRow2 As Integer
Dim rStartCell2 As Range

Set rStartCell2 = Sheet2.Range("A655").End(xlUp)

For iListCount2 = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(iListCount2) = True Then
ListBox1.Selected(iListCount2) = False
iRow2 = iRow2 + 1
rStartCell2.Offset(iRow2).Value = ListBox1.List(iListCount2, 0)
End If
Next iListCount2
Set rStartCell2 = Nothing


End If
End Sub

By the way, I have to do the same for 13 sheets more, do you know a simple code too?

And always thanks for your help
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
First let me complement you on trying to solve the problem on your own. You have been doing great. In fact your attempts helps me in answering you more quickly as you can see from below, I took your idea and your code and only slightly modified it. So again. You did great job.


Try this

Private Sub CommandButton1_Click()  
Dim sTgtSht As String 
Dim rngLastCell As Range 
Dim lLastRow As Long 
Dim sActiveSheet As String 
     
    sActiveSheet = ActiveSheet.Name 
     
    sTgtSht = ComboBox1.Value 
     
    If (sTgtSht = "") Then 
        MsgBox "No target sheet select." 
        GoTo End_Sub 
    End If 
         
    On Error Resume Next 
        Sheets(sTgtSht).Select 
        Err.Clear 
    On Error GoTo 0 
     
    If ActiveSheet.Name <> Sheets(sTgtSht).Name Then 
        MsgBox "Sheet '" & sTgtSht & "' not found." 
        GoTo End_Sub 
    End If 

    Set rngLastCell = Sheets(sTgtSht).Cells.Find("*", Cells(1, 1), , , xlByRows, xlPrevious) 
     
    If rngLastCell Is Nothing Then 
        lLastRow = 1 
    Else 
        lLastRow = rngLastCell.Row 
    End If 
         
    Set rngLastCell = Nothing 
     
    For iListCount = 0 To ListBox1.ListCount - 1 
     
        If ListBox1.Selected(iListCount) = True Then 
             
            lLastRow = lLastRow + 1 
            ListBox1.Selected(iListCount) = False 
            Sheets(sTgtSht).Cells(lLastRow, "A") = ListBox1.List(iListCount, 0) 
         
        End If 
         
    Next iListCount 
         
End_Sub: 
    Sheets(sActiveSheet).Select 
     
End Sub
Hi That is great but still only copying os transfering the first column of the listbox1 and not the two columns. Do you know why?

Thanks a lot
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Because in the code, you had like this. I thought that was by design
add this line
Sheets(sTgtSht).Cells(lLastRow, "B") = ListBox1.List(iListCount, 1)


The above line will go under
Sheets(sTgtSht).Cells(lLastRow, "A") = ListBox1.List(iListCount, 0)


so you would have
Sheets(sTgtSht).Cells(lLastRow, "A") = ListBox1.List(iListCount, 0)
Sheets(sTgtSht).Cells(lLastRow, "B") = ListBox1.List(iListCount, 1)
YOU DO NOT KNOW HOW MUCH I APPRECIATE YOUR HELP. Thanks for the complements but you are the one who deserves it.

I think we can say problem solved

HUge, huge thanks rizvisa
Umm,, before we close correct if iam wrong. You help me out with the next code, is there some way to clear the two columns once they are transfer to the new sheet so the user does not get confuse with the last records. I know hot to clear results, but this bussiness with the columns sometimes is confusing for me.

Private Sub CommandButton1_Click()
Dim sTgtSht As String
Dim rngLastCell As Range
Dim lLastRow As Long
Dim sActiveSheet As String

sActiveSheet = ActiveSheet.Name

sTgtSht = ComboBox1.Value

If (sTgtSht = "") Then
MsgBox "No target sheet select."
GoTo End_Sub
End If

On Error Resume Next
Sheets(sTgtSht).Select
Err.Clear
On Error GoTo 0

If ActiveSheet.Name <> Sheets(sTgtSht).Name Then
MsgBox "Sheet '" & sTgtSht & "' not found."
GoTo End_Sub
End If

Set rngLastCell = Sheets(sTgtSht).Cells.Find("*", Cells(1, 1), , , xlByRows, xlPrevious)

If rngLastCell Is Nothing Then
lLastRow = 1
Else
lLastRow = rngLastCell.Row
End If

Set rngLastCell = Nothing

For iListCount = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(iListCount) = True Then

lLastRow = lLastRow + 1
ListBox1.Selected(iListCount) = False
Sheets(sTgtSht).Cells(lLastRow, "A") = ListBox1.List(iListCount, 0)
Sheets(sTgtSht).Cells(lLastRow, "B") = ListBox1.List(iListCount, 1)

End If

Next iListCount

End_Sub:
Sheets(sActiveSheet).Select

End Sub