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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

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

New Member
Joined
May 11, 2011
Messages
5
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

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?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

fredz0003

New Member
Joined
May 11, 2011
Messages
5
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.
 

Forum statistics

Threads
1,141,850
Messages
5,708,982
Members
421,602
Latest member
jkpce1880

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
Top