Drop down list quicker selection
We are using Excel 2003
We created a drop down list using the Data\Validation process, which works fine. When the arrow within a cell is clicked on to show the drop down list, how can you quickly select one of the many names in the list just by typing the first character of a name within the list. At the moment, we have to scroll up and down the list to select a particular name, which in itself isn't a problem, but slows down the process of finding the right name. Can the names within the list be alphabetically grouped when inputting the first character from the keyboard, and speed up the selection. I hope this makes sense! Any help would be appreciated. Thanks Nick |
Drop down list quicker selection
When the arrow within a cell is clicked on to show the drop down list, how
can you quickly select one of the many names in the list just by typing the first character of a name within the list. Debra Dalgleish shows a way to "build in" the AutoComplete feature into DVs, which finds matching items in the list as you start to type .. http://www.contextures.com/xlDataVal10.html -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100, Files:360, Subscribers:56 xdemechanik --- |
Drop down list quicker selection
Hi Nick
In order to use Autocomplete, you need to use a Combo Box. With regard to listing the entries from the first character, this can be done with Data Validation with the aid of a macro. I have a sample workbook on Debra Dalgleish's site http://www.contextures.com/DataValHeadings.zip -- Regards Roger Govier "Nick" wrote in message ... We are using Excel 2003 We created a drop down list using the Data\Validation process, which works fine. When the arrow within a cell is clicked on to show the drop down list, how can you quickly select one of the many names in the list just by typing the first character of a name within the list. At the moment, we have to scroll up and down the list to select a particular name, which in itself isn't a problem, but slows down the process of finding the right name. Can the names within the list be alphabetically grouped when inputting the first character from the keyboard, and speed up the selection. I hope this makes sense! Any help would be appreciated. Thanks Nick |
Drop down list quicker selection
Hi Roger
Thanks very much for your swift response! I'm still unsure though, how to go about setting the macro up from the example. Is there a link to a tutorial for this? Thanks again Nick "Roger Govier" wrote: Hi Nick In order to use Autocomplete, you need to use a Combo Box. With regard to listing the entries from the first character, this can be done with Data Validation with the aid of a macro. I have a sample workbook on Debra Dalgleish's site http://www.contextures.com/DataValHeadings.zip -- Regards Roger Govier "Nick" wrote in message ... We are using Excel 2003 We created a drop down list using the Data\Validation process, which works fine. When the arrow within a cell is clicked on to show the drop down list, how can you quickly select one of the many names in the list just by typing the first character of a name within the list. At the moment, we have to scroll up and down the list to select a particular name, which in itself isn't a problem, but slows down the process of finding the right name. Can the names within the list be alphabetically grouped when inputting the first character from the keyboard, and speed up the selection. I hope this makes sense! Any help would be appreciated. Thanks Nick |
Drop down list quicker selection
Hi Nick
With my example file open, and the file in which you wish to use the macros, Press Alt+F11 to invoke the Visual Basic Editor. In the Project Explorer (press Control+R if it is not visible), double click on VBAProject DataValHeadings.xls This will show a list of the Sheets in the Workbook, and Modules. Double click the Modules, and you will see Module1 Inside there, you will see 3 different macros which are used to Remove all letters, Sort and Add all letters of the alphabet, and Sort and Add only letters used in your List. The easiest way to copy these to your workbook, is to click on Module1, hold down the left mouse key as you drag the module and just drop it on your workbook. This will copy the Module to your workbook. Either ensure that teh sheet in your Workbook which contains your list of items is named List, or change the name in the macros from Worksheets("List").Activate to whatever the name of your sheet is. To use just type Alt+F8 to bring up the macro listhighlight the relevant macroRun -- Regards Roger Govier "Nick" wrote in message ... Hi Roger Thanks very much for your swift response! I'm still unsure though, how to go about setting the macro up from the example. Is there a link to a tutorial for this? Thanks again Nick "Roger Govier" wrote: Hi Nick In order to use Autocomplete, you need to use a Combo Box. With regard to listing the entries from the first character, this can be done with Data Validation with the aid of a macro. I have a sample workbook on Debra Dalgleish's site http://www.contextures.com/DataValHeadings.zip -- Regards Roger Govier "Nick" wrote in message ... We are using Excel 2003 We created a drop down list using the Data\Validation process, which works fine. When the arrow within a cell is clicked on to show the drop down list, how can you quickly select one of the many names in the list just by typing the first character of a name within the list. At the moment, we have to scroll up and down the list to select a particular name, which in itself isn't a problem, but slows down the process of finding the right name. Can the names within the list be alphabetically grouped when inputting the first character from the keyboard, and speed up the selection. I hope this makes sense! Any help would be appreciated. Thanks Nick |
Drop down list quicker selection
Many thanks Roger for your concise description. It worked well!
Regards Nick "Roger Govier" wrote: Hi Nick With my example file open, and the file in which you wish to use the macros, Press Alt+F11 to invoke the Visual Basic Editor. In the Project Explorer (press Control+R if it is not visible), double click on VBAProject DataValHeadings.xls This will show a list of the Sheets in the Workbook, and Modules. Double click the Modules, and you will see Module1 Inside there, you will see 3 different macros which are used to Remove all letters, Sort and Add all letters of the alphabet, and Sort and Add only letters used in your List. The easiest way to copy these to your workbook, is to click on Module1, hold down the left mouse key as you drag the module and just drop it on your workbook. This will copy the Module to your workbook. Either ensure that teh sheet in your Workbook which contains your list of items is named List, or change the name in the macros from Worksheets("List").Activate to whatever the name of your sheet is. To use just type Alt+F8 to bring up the macro listhighlight the relevant macroRun -- Regards Roger Govier "Nick" wrote in message ... Hi Roger Thanks very much for your swift response! I'm still unsure though, how to go about setting the macro up from the example. Is there a link to a tutorial for this? Thanks again Nick "Roger Govier" wrote: Hi Nick In order to use Autocomplete, you need to use a Combo Box. With regard to listing the entries from the first character, this can be done with Data Validation with the aid of a macro. I have a sample workbook on Debra Dalgleish's site http://www.contextures.com/DataValHeadings.zip -- Regards Roger Govier "Nick" wrote in message ... We are using Excel 2003 We created a drop down list using the Data\Validation process, which works fine. When the arrow within a cell is clicked on to show the drop down list, how can you quickly select one of the many names in the list just by typing the first character of a name within the list. At the moment, we have to scroll up and down the list to select a particular name, which in itself isn't a problem, but slows down the process of finding the right name. Can the names within the list be alphabetically grouped when inputting the first character from the keyboard, and speed up the selection. I hope this makes sense! Any help would be appreciated. Thanks Nick |
All times are GMT +1. The time now is 02:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com