VBA code needed

[ernie]

i am trying to retrieve a range of data (the stock code of different phones)
from worksheet("Inventory") based on the name of the branches of where the
phones are stored.

worksheet("Inventory") has branch (Column A) and stock code (Column B)

i have 2 combobox.
combobox1 contains values (name of branches) such as:
1-BS
2-EN
3-HG
4-JE
5-SP
6-TB
7-WS
8-YT

combobox2 will have to retrieve values from the worksheet("Inventory") based
on the value in combobox1. the values in combobox1 can be found in column 1
while the values i need for combobox2 can be retrieved from column 2 of
Inventory. however, i have many values for each branch, for example, i have
many 1-BS and there are many stock codes linked to 1-BS. how to retrieve all
the stock code of that particular branch?

Thanks.

 

[Dave Peterson]

In the combobox1_change procedure, you could loop through column A and if it
matches values, then add a new item to combobox2.

Option Explicit
Private Sub ComboBox1_Change()
Dim myRng As Range
Dim myCell As Range

If Me.ComboBox1.ListIndex < 0 Then
Exit Sub 'nothing chosen
End If

With Worksheets("Inventory")
Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ComboBox2
.Clear
For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
.AddItem myCell.Offset(0, 1).Value
End If
Next myCell
End With

End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

With Me.ComboBox1
.RowSource = ""
.Clear
'just some test data
.AddItem "A1"
.AddItem "A2"
.AddItem "A3"
.AddItem "A4"
End With

End Sub