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

gssachin

Board Regular
Joined
Nov 14, 2013
Messages
127
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.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,929
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

gssachin

Board Regular
Joined
Nov 14, 2013
Messages
127
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.
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,929
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Nov 14, 2013
Messages
127

ADVERTISEMENT

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

Well-known Member
Joined
Dec 5, 2002
Messages
7,929
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Nov 14, 2013
Messages
127

ADVERTISEMENT

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

Well-known Member
Joined
Dec 5, 2002
Messages
7,929
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,619
Messages
5,523,938
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top