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]

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

 

[abdo meghari]

Hi
just see the INVOICE sheet and from row 21 will understand with details in OP .
there is cod in sheet module.
DATA VALIDATION.xlsm

 

[Akuini]

I've downloaded your file.

when select combobox1 =BS 1200R20, then populate in combobox2 =G580 ,R187 , and when select combobox2=G580 then will populate JAP,THI in combobox3

What is the combobox for? Is it only to show the information?
Or also to send the information to a specific cell? if so, to what cell?

 

[abdo meghari]

What is the combobox for?

not just show in three comboboxes ,also in INVOICE sheet should select the item and populate in the cell to issue new invoice
and when write the item in combobox1 then will filter tje whole item until finishing write and populate in the cell where contain the combo box
for instance when select data validation B21 "BS 1200R20" then will populate item in B21 so the same thing when select combobox1 B21 "BS 1200R20" then will populate item in B21 .
three comboboxes should show for each cell to select item until TOTAL row in IVOICE sheet.
the items in three comboboxes will be linked with columns B,C,D in BRAND sheet .

 

[Akuini]

three comboboxes should show for each cell

I don't understand, you want to put the combobox on each cell?
So 3 combobox on each row? on how many rows? say if there are 10 rows then there would be 30 combobox?

 

[abdo meghari]

Yes should put combobox on each cell
Yes 3 comboboxes on each row
Exactly would be 30 comboboxes as your example,but there's point when start comboboxes should be from row21 and finish to before Total row ,should ignore the Total row from addition of comboboxes .

 

[Akuini]

Yes should put combobox on each cell
Yes 3 comboboxes on each row
Exactly would be 30 comboboxes as your example,but there's point when start comboboxes should be from row21 and finish to before Total row ,should ignore the Total row from addition of comboboxes .

I'm not a fan of having too many activex objects on a single sheet, as they can be unstable.
How about using a userform with just 3 combobox.
The comboboxes are cascading, as you said:
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
You can activate the userform by double clicking the cell > search the information in the combobox > send the information to the cell.

 

[abdo meghari]

You can activate the userform by double clicking the cell > search the information in the combobox > send the information to the cell.

ok if you see this better way ,but I want cancelling data validation from showing , I don't need it if I use combo boxes.

 

[Akuini]

ok if you see this better way ,but I want cancelling data validation from showing , I don't need it if I use combo boxes.

OK, I'll try to write the code tomorrow.