If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VBA code needed
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. -- help me |
#2
|
|||
|
|||
VBA code needed
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 ernie wrote: 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. -- help me -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|