Help Please! Use Combobox selection to find and pull data from sheet to listbox?

[kinetekerz]

Hi all and a happy new year!

I am looking for help with a basic user form that has:

1x Combo box.
1x List box

I need it to pull data from 4 columns on Sheet 1. If the combo box selection states "Factory" then it needs to pull the stated data below from the matches to "Factory" in Column I.

So to break it down;

Combobox selection finds matches in column I.
The matches must then be pulled to the list box with;

First name from Column C
Last name from Column D
Number from Column H
Industry area from Column I (The column we use to search and filter)

Please ask if it is not explained well enough. I have already populated combo box with row source.

Many thanks.

Sam.

 

[MickG]

Try this code in Userform Combobox.

Private Sub ComboBox1_Change()
Dim Rng As Range, Dn As Range, p As Variant, c As Long, Ac As Integer
With Sheets("Sheet1")
    Set Rng = .Range(.Range("I1"), .Range("I" & Rows.Count).End(xlUp))
End With
ReDim ray(1 To Rng.Count, 1 To 4)
    For Each Dn In Rng
        If Dn.Value = ComboBox1.Value Then
            c = c + 1: Ac = 0
            For Each p In Array(-6, -5, -1, 0)
                Ac = Ac + 1
                ray(c, Ac) = Dn.Offset(, p).Value
            Next p
        End If
Next Dn
With ListBox1
    .Clear
    .ColumnCount = 4
    .ColumnWidths = "50,50,50,50"
    .List = ray
End With
End Sub

 

[kinetekerz]

Try this code in Userform Combobox.

Private Sub ComboBox1_Change()
Dim Rng As Range, Dn As Range, p As Variant, c As Long, Ac As Integer
With Sheets("Sheet1")
    Set Rng = .Range(.Range("I1"), .Range("I" & Rows.Count).End(xlUp))
End With
ReDim ray(1 To Rng.Count, 1 To 4)
    For Each Dn In Rng
        If Dn.Value = ComboBox1.Value Then
            c = c + 1: Ac = 0
            For Each p In Array(-6, -5, -1, 0)
                Ac = Ac + 1
                ray(c, Ac) = Dn.Offset(, p).Value
            Next p
        End If
Next Dn
With ListBox1
    .Clear
    .ColumnCount = 4
    .ColumnWidths = "50,50,50,50"
    .List = ray
End With
End Sub

Your a Diamond that worked a treat!

After looking at the code to see about inputting another column (N) I can't see how you have added the other columns? Would you mind explaining how to add another column to the code?

Many thanks!

 

[MickG]

See new code Comments for addition of column "N".

Dim Rng As Range, Dn As Range, p As Variant, c As Long, Ac As Integer
With Sheets("Sheet1")
    Set Rng = .Range(.Range("I1"), .Range("I" & Rows.Count).End(xlUp))
End With
'Extra column added (4 to 5)
ReDim ray(1 To Rng.Count, 1 To 5)
    For Each Dn In Rng
        If Dn.Value = ComboBox1.Value Then
            c = c + 1: Ac = 0
      
      '(5) added, for column "N" which is 5 columns offset from "I"
            For Each p In Array(-6, -5, -1, 0, 5)
                Ac = Ac + 1
                ray(c, Ac) = Dn.Offset(, p).Value
            Next p
        End If
Next Dn
With ListBox1
    .Clear
    .ColumnCount = 5 'Column added
    .ColumnWidths = "50,50,50,50,50" 'Column added
    .List = ray
End With
End Sub

 

[kinetekerz]

See new code Comments for addition of column "N".

Dim Rng As Range, Dn As Range, p As Variant, c As Long, Ac As Integer
With Sheets("Sheet1")
    Set Rng = .Range(.Range("I1"), .Range("I" & Rows.Count).End(xlUp))
End With
'Extra column added (4 to 5)
ReDim ray(1 To Rng.Count, 1 To 5)
    For Each Dn In Rng
        If Dn.Value = ComboBox1.Value Then
            c = c + 1: Ac = 0
      
      '(5) added, for column "N" which is 5 columns offset from "I"
            For Each p In Array(-6, -5, -1, 0, 5)
                Ac = Ac + 1
                ray(c, Ac) = Dn.Offset(, p).Value
            Next p
        End If
Next Dn
With ListBox1
    .Clear
    .ColumnCount = 5 'Column added
    .ColumnWidths = "50,50,50,50,50" 'Column added
    .List = ray
End With
End Sub

Cheers Mick, i see how you've incorporated that, brilliant!

Apologies for another question;

I was planning on formatting one of the columns (Column H) so that it shows the "0" before the phone number. I had achieved this on the sheet via the standard 11x "0" formatting and had planned on implementing some kind of list formatting like this;

[COLOR=#333333].List(lngIndex, 4) = (Format(Val(.List(lngIndex, 4)), "00000000000"))[/COLOR]

But i understand it wouldn't be compatible with the code you have put forward, is there a workaround?

Many thanks!

 

[MickG]

When I format column "H" ( 11 digit numbers with leading zero) as text and run the code the number with the leading Zero shows in the listbox list, what do you get ?????

 

[kinetekerz]

When I format column "H" ( 11 digit numbers with leading zero) as text and run the code the number with the leading Zero shows in the listbox list, what do you get ?????

Currently my Column H is formatted as custom and then "00000000000" (This wil show the 0 before a umber on the sheet correctly)

Upon searching, the column in the list box hasn't got formatting, shows only 10 numbers due to no "0" at the start.

 

[MickG]

What happens if you format column "H" as "Text".

 

[kinetekerz]

What happens if you format column "H" as "Text".

It loses the "0" in sheets and returns to 10 digit number

 

[kinetekerz]

The listbox also still has no "0's"