using dropdown(combobox) instead of data validation to autocomplete items

[abdo meghari]

Hi Guys,
I search for using three combo boxes instead of data validation
combobox1 will be in column B,combobox2 will be in column C,combobox3 will be in column D, and should start from row21
when I select combobox1 should populate item in combobox2 based on combobox1 , when select combobox2 then should populate item in combobox 3 based on combobox1,2
for instance :
I have data like this
column B,C,D in BRAND sheet from row2
B C D
BS 1200R20 G580 JAP
BS 1200R20 G580 THI
BS 1200R20 R187 THI
BS 1400r20 VS JAP
combobox1,2,3 are existed in INVOICE sheet from row 21
when select combobox1 =BS 1200R20, then populate in combobox2 =G580 ,R187 , and when select combobox2=G580 then will populate JAP,THI in combobox3
so I don't want any duplicates items in comboboxes 1,2,3 when select specific item as in BS 1200R20 and when write ITEM for instance in combobox1 BS 1200R20 should autocomplete and filter list contains based on written .
I'm talking about OLEObjects(combobox), not on userform .
thanks in advance .

 

[Akuini]

Try this example:
I haven't implemented the search feature yet. See if it works for you then I'll finish the code.
The workbook:

abdo meghari - 3 combobox, dependent, userform 1

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[abdo meghari]

great !
this file is really comfortable .
many thanks for this work .

 

[Akuini]

Ok, this version already has the searchable feature, you can type multiple keyword separated by a space.

You need to amend the code in this part:

In module1:

'range where you want to use the combobox [in the sample: "D5:F8" in "sheet1", the green area)
Public Const xCell As String = "D5:F8"   '###

In Userform1:

Private Sub UserForm_Initialize()
Dim i As Long
    '### DATA SOURCE to populate combobox, in this example data source is in Sheets("deList"), col A:C, start at A2
    With Sheets("deList")
        vList = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 3)
    End With

The work book:

abdo meghari - 3 combobox, dependent, userform 2

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[abdo meghari]

Fantastic!

thank you so much .

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[Akuini]

@abdo meghari
Sorry, the code is bit flawed regarding the 3rd combobox.
You need to add this code in the Userform1 code module:

Private Sub ComboBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

nFlag = False
With Me.ComboBox3
    Select Case KeyCode
       
        Case vbKeyDown, vbKeyUp
             nFlag = True 'don't change the list when combobox1 value is changed by DOWN ARROW or UP ARROW key
         
    End Select
 
End With
End Sub