ComboBox ListIndex definition

[GDunn]

Hi,

I am trying to change the ListIndex of a ComboBox(2) based on a value driven from a previous ComboBox(1).

- ComboBox(1) is a customer drop-down list on Sheets("Sheet1")
- Based on ComboBox(1) selection Sheets("Sheet1").Range("F1") is updated with a lookup value
- Sheets("Sheet1").Range("F1") value will match value in column in DynamicRange 'ItemCode' on Sheets("Items")
- Need to drive ComboBox(2).ListIndex by using DynamicRange 'ItemCode' on Sheets("Items") where line value = Sheets("Sheet1").Range("F1")

Hope this makes sense. I am struggling to find any answers to help me on this one.

Thanks

 

[GlennUK]

Are you saying that when someone chooses a customer in ComboBox(1) then some matching information is to be displayed in ComboBox(2)?

Are the lists related in some way? Maybe they exist side-by-side somewhere?

And why are you using a combobox to show the info, instead of say a TextBox?

 

[GDunn]

Customer selection in ComboBox1 will trigger a lookup reference in cell F1
i.e. 'ABC1'

'ABC1' in cell F1 will also exist in column A of sheet 'Items', but is listed with rows 'ABC2', 'ABC3' ......... also in column A.

I want to return only the rows with ABC1 in column A of 'Items' in ComboBox2. This is the trigger.

Any help gratefully received.
I have actually built this with the full item list and it is confusing the ComboBox2 which returns incorrect values when selected with Calculation on Automatic (I assume there is a list restriction...)

 

[fredz0003]

Are you saying that when someone chooses a customer in ComboBox(1) then some matching information is to be displayed in ComboBox(2)?

Are the lists related in some way? Maybe they exist side-by-side somewhere?

And why are you using a combobox to show the info, instead of say a TextBox?

Hi Glenn I am trying to do that, two combo boxes and the reason why I use two combo boxes is because the first one is a part number and the second one the part description, if I type in the part number I would like the second one to auto populate, same with the second box if I type in the description it should auto populate in box 1. Both lists are side by side on a different worksheet, thanks for the help and for bumping in this forum

 

[GlennUK]

Hi Glenn I am trying to do that, two combo boxes and the reason why I use two combo boxes is because the first one is a part number and the second one the part description, if I type in the part number I would like the second one to auto populate, same with the second box if I type in the description it should auto populate in box 1. Both lists are side by side on a different worksheet, thanks for the help and for bumping in this forum

Are the combo boxes embedded in a worksheet, or are they part of a userform?

 

[fredz0003]

Are the combo boxes embedded in a worksheet, or are they part of a userform?

Embedded on the spreadsheet.

 

[GlennUK]

Then have some code to align the entries, similar to this ( in the sheet code area ):

Private Sub ComboBox1_Change()
    If ComboBox1.Value <> "" Then
        ComboBox2.Value = Sheet3.Range("Desc").Cells(Application.Match(ComboBox1.Value, Sheet3.Range("Code"), 0))
    Else
        ComboBox2.Value = ""
    End If
End Sub
Private Sub ComboBox2_Change()
    If ComboBox2.Value <> "" Then
        ComboBox1.Value = Sheet3.Range("Code").Cells(Application.Match(ComboBox2.Value, Sheet3.Range("Desc"), 0))
    Else
        ComboBox1.Value = ""
    End If
End Sub

... I had named ranges for Code and Desc in Sheet3. Change for your case.

 

[fredz0003]

Then have some code to align the entries, similar to this ( in the sheet code area ):

Private Sub ComboBox1_Change()
    If ComboBox1.Value <> "" Then
        ComboBox2.Value = Sheet3.Range("Desc").Cells(Application.Match(ComboBox1.Value, Sheet3.Range("Code"), 0))
    Else
        ComboBox2.Value = ""
    End If
End Sub
Private Sub ComboBox2_Change()
    If ComboBox2.Value <> "" Then
        ComboBox1.Value = Sheet3.Range("Code").Cells(Application.Match(ComboBox2.Value, Sheet3.Range("Desc"), 0))
    Else
        ComboBox1.Value = ""
    End If
End Sub

... I had named ranges for Code and Desc in Sheet3. Change for your case.

Thanks a lot Glenn that worked perfect I really need to start building some sort of database and I'm currently going to school for c++ programming so I'm trying to learn a little bit of vba, but is a lot different. One more question if you would. When I typed something that is not in the list it gives me a bug error, I understand because it is bound to that list, how can I implement an if statement to add new items to the list? I hope that makes sense.