Populate Sheet based on ComboBox selection

[Steve]

Hello. On Sheet2 I have a combo box. On sheet1 ColA, I have the
"key" for the combobox selections. So if I choose "Product1" from the
combobox, how do I have vba bring in all the rows that have "Product1"
in Column A? Bringing in the entire row, or columns 1 thru 8 would be
fine. Thanks!!

 

[JP]

What do you mean by "bring in" ? Hide?

--JP

 

[Steve]

No, more like copy the row from Sheet1 and paste it in Sheet2. Sheet1
will house all the data and be hidden. The combobox is on Sheet2, so
i would like the appropriate rows ro be pasted beginning on Row5.
Thanks!!

 

[JP]

Is the combo box on a userform? It's much easier that way. I created a
test userform with a single combo box which takes the range in sheet
1, column A and copies matching rows to Sheet 2. It's not much but it
does exactly what you want.

Here is the code behind the userform:

Private Sub CommandButton1_Click()
Dim cell As Excel.Range
Dim rng As Excel.Range

For Each cell In Worksheets("Sheet1").Range("A1",
Range("A65536").End(xlUp))
Set rng = Worksheets("Sheet2").Range("A4")

If CLng(cell.Value) = ComboBox1.Value Then
cell.EntireRow.Copy rng.Offset(1, 0)
End If

Next cell
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim cell As Excel.Range

For Each cell In Worksheets("Sheet1").Range("A1",
Range("A65536").End(xlUp))
ComboBox1.AddItem CLng(cell.Value)
Next cell

End Sub


If you like I can email you a copy.

HTH,
JP

 

[Steve]

Hi JP. Thanks for the responses. My combo box is NOT in a user
form. Didn't want to mess with loading the form or clicking the "go"
button. My combo box populates based on the user id and happend via
an open-workbook event. Is the code dramatically different if i use
the combo box from the control toolbox? THats how i have the file
built now...rather not mess with it! Thanks JP!!

 

[sankari]

populate unique data in a combobox

Hi,

Column A contains projects list
Column E contains Division List
A E

Proj1 Div1
Proj2 Div2
Proj3 Div1

in Combobox1 unique divisions need to be populated. Combobox2 should have projects. On selecting a division, projects pertaining to the division should be displayed.

any hlp is shighly obliged