How to get Drop down list/Customs List with auto-complete feature

[gssachin]

Hi,
I have around 100 carders which is located in Sheet (“Master Carders List”)

And I have to apply some carders to around 200 rows (employees) in Sheet 1 (“G” Column, Starting G3:G200) .
Since User should not make any spell mistake or make new carder for that I want to restrict them to use carder in “Master Carder List”.

I tried data validation “Custom List” option but its time consuming task to select correct cadre out of 200 cadres .
Therefore I required “Custom List” with “Auto-Complete” Feature so we can save time for users.

Thanks in advance.

 

[Jaafar Tribak]

Hi,
I have around 100 carders which is located in Sheet (“Master Carders List”)

And I have to apply some carders to around 200 rows (employees) in Sheet 1 (“G” Column, Starting G3:G200) .
Since User should not make any spell mistake or make new carder for that I want to restrict them to use carder in “Master Carder List”.

I tried data validation “Custom List” option but its time consuming task to select correct cadre out of 200 cadres .
Therefore I required “Custom List” with “Auto-Complete” Feature so we can save time for users.

Thanks in advance.

Auto-Complet with Data Validation is difficult.

You may be intersested in this

Here is a fresh workbook example.

 

[gssachin]

Dear Jaffar Sir,

Thanks for your reply. Its work's the way I want. Thanks you very much.

I changed my source data from same sheet to "Sheet2" and works fine Only I need change "(InputCells As String = "E6,E7,E14,G6,G14")" to ("InputCells As String = "E6:E240") Since I have to use more than 200 rows as Input cells.

Once again thank you & waiting your favorable reply.

 

[Jaafar Tribak]

Dear Jaffar Sir,

Thanks for your reply. Its work's the way I want. Thanks you very much.

It it possible to keep My List in Another Sheet (i.e. "Master Cadre") also I have more than 200 rows so can change "(InputCells As String = "E6,E7,E14,G6,G14")" to ("InputCells As String = "E6:e240")

Once again thank you & waiting your favorable reply.

Are you saying that you have various input cells not just one ? and are all the input cells in the same sheet ?

 

[gssachin]

Are you saying that you have various input cells not just one ? and are all the input cells in the same sheet ?

Yes Sir, I have more than 200 rows (which every month will change depend on new employees) & All input I have entered in same sheet (i.e. sheet1)

 

[Jaafar Tribak]

Yes Sir, I have more than 200 rows (which every month will change depend on new employees) & All input I have entered in same sheet (i.e. sheet1)

Ok, I wrote the code a while ago and it is rather complicated so it will take me some time to try and edit it in order for it to work on different sheets.

I'll post back later on.

 

[gssachin]

Sir,

Sorry for troubling.

Is it possible to get drop down list even alphabets used in middle in list name
for example I have data like > "mechanical fitter", "instrum. fitter", "elect. fitter" so if I entered only "fitter" then drop down list will show all this three items so used can select which he want by clicking on that name.

 

[Jaafar Tribak]

Sir,

Sorry for troubling.

Is it possible to get drop down list even alphabets used in middle in list name
for example I have data like > "mechanical fitter", "instrum. fitter", "elect. fitter" so if I entered only "fitter" then drop down list will show all this three items so used can select which he want by clicking on that name.

That would complicate things too much .

As for having the input cells in a different sheet from the master list sheet as you requested , here is a workbook example

Note that the code has two drawbacks :

1- After you input data in the inputcells , the excel Undo functionnality is lost.
2- Because the code dynamically inserts an ActiveX textbox and listbox on the worksheet, any data stored in public variables are lost in the process.

 

[gssachin]

Dear Sir,

Thanks you very much.

 

[Jaafar Tribak]

Dear Sir,

Thanks you very much.

You are welcome.