ComboBox ListIndex definition

GDunn

Board Regular
Joined
Mar 24, 2009
Messages
51
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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?
 
Upvote 0
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...)
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Then have some code to align the entries, similar to this ( in the sheet code area ):
Code:
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.
 
Upvote 0
Then have some code to align the entries, similar to this ( in the sheet code area ):
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top