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

kinetekerz

Board Regular
Joined
Jun 11, 2015
Messages
53
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.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this code in Userform Combobox.

Code:
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

Board Regular
Joined
Jun 11, 2015
Messages
53
Try this code in Userform Combobox.

Code:
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
See new code Comments for addition of column "N".
Code:
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
 
Last edited:

kinetekerz

Board Regular
Joined
Jun 11, 2015
Messages
53

ADVERTISEMENT

See new code Comments for addition of column "N".
Code:
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;

Code:
[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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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

Board Regular
Joined
Jun 11, 2015
Messages
53

ADVERTISEMENT

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,860
Messages
5,525,257
Members
409,639
Latest member
nithin49

This Week's Hot Topics

Top