Show unique list in listbox

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

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

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

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