Auto populate Drop down box with 2 different options

[Ben171]

Hi there, going to try and explain this clearly because it can get confusing,

I have a user form which uses various lookup methods to auto fill data. But for this explanation lets say i am just looking at 2 combo boxes on the form; Descriptioncmb and PartNocmb

We are looking at the following two columns in a data sheet:

The Description on the userform is chosen through the ComboBox options, which in this brief description, there will just be one option (pipe). then PartNocmb needs to be filled based on the description set.

I've done this through the use of a VLOOKUP;

Dim MyTableArray As Range

Set MyTableArray = Sheets("Data").Range("A:B")

'vlookup
Me.PartNocmb = WorksheetFunction.VLookup(Me.Descriptioncmb, MyTableArray, 2, 0)

This works great, however we have now started to do conversions, meaning sometimes the part number me have a /5 on the end instead of a /3.

Is there a way to implement this to allow the option for a user to choose between the 1126/3 and 1126/5. I was thinking they could both be displayed in the PartNocmb and the user can select which one to use?

Hope that makes sense, really hard to explain!

 

[fadee2]

Hy ben171,
How about using autofilter to filter out your list and add matching results to combobox on the fly, instead of using vlookup?

Private Sub ComboBox1_AfterUpdate()

'filter list based on Combobox1 value
Sheets("sheet1").UsedRange.AutoFilter Field:=1, Criteria1:=ComboBox1.Value

'focus filtered range
Set frng = Sheets("sheet1").Range("b2", Sheets("sheet1").Cells(Rows.Count, "b").End(xlUp)).SpecialCells(xlCellTypeVisible)

'clear Combobox2 list
ComboBox2.Clear

'Add visible items to Combobox2 List
For Each cell In frng
 ComboBox2.AddItem cell.Value
Next cell

'clear out applied filter
Sheets("sheet1").UsedRange.AutoFilter

'display Combobox2 dropdown
ComboBox2.DropDown

End Sub

hope this helps....

 

[Ben171]

Hy ben171,
How about using autofilter to filter out your list and add matching results to combobox on the fly, instead of using vlookup?

Private Sub ComboBox1_AfterUpdate()

'filter list based on Combobox1 value
Sheets("sheet1").UsedRange.AutoFilter Field:=1, Criteria1:=ComboBox1.Value

'focus filtered range
Set frng = Sheets("sheet1").Range("b2", Sheets("sheet1").Cells(Rows.Count, "b").End(xlUp)).SpecialCells(xlCellTypeVisible)

'clear Combobox2 list
ComboBox2.Clear

'Add visible items to Combobox2 List
For Each cell In frng
 ComboBox2.AddItem cell.Value
Next cell

'clear out applied filter
Sheets("sheet1").UsedRange.AutoFilter

'display Combobox2 dropdown
ComboBox2.DropDown

End Sub

hope this helps....

View attachment 49870

This looks like a great idea, i will give this a go. Thanks!

 

[Ben171]

Hy ben171,
How about using autofilter to filter out your list and add matching results to combobox on the fly, instead of using vlookup?

Private Sub ComboBox1_AfterUpdate()

'filter list based on Combobox1 value
Sheets("sheet1").UsedRange.AutoFilter Field:=1, Criteria1:=ComboBox1.Value

'focus filtered range
Set frng = Sheets("sheet1").Range("b2", Sheets("sheet1").Cells(Rows.Count, "b").End(xlUp)).SpecialCells(xlCellTypeVisible)

'clear Combobox2 list
ComboBox2.Clear

'Add visible items to Combobox2 List
For Each cell In frng
 ComboBox2.AddItem cell.Value
Next cell

'clear out applied filter
Sheets("sheet1").UsedRange.AutoFilter

'display Combobox2 dropdown
ComboBox2.DropDown

End Sub

hope this helps....

View attachment 49870

What would you define the variable frng as?

 

[fadee2]

What would you define the variable frng as?

'focus filtered range
Set frng = Sheets("sheet1").Range("b2", Sheets("sheet1").Cells(Rows.Count, "b").End(xlUp)).SpecialCells(xlCellTypeVisible)

frng is set to focus filtered range

 

[Ben171]

'focus filtered range
Set frng = Sheets("sheet1").Range("b2", Sheets("sheet1").Cells(Rows.Count, "b").End(xlUp)).SpecialCells(xlCellTypeVisible)

frng is set to focus filtered range

Maybe i have messed up somewhere?

Combobox1 is called ModelNo
Combobox2 is called PartNo

 

[fadee2]

try

dim frng as range
Set frng = Sheets("sheet1").Range("b2", Sheets("sheet1").Cells(Rows.Count, "b").End(xlUp)).SpecialCells(xlCellTypeVisible)