New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Drop-down Selection Lists

Free Excel Macros

Drop-down Selection Lists

Postby Sisyphus » Thu Jun 05, 2014 11:18 pm

Excel offers drop-down validation lists which can be used as selection lists. Unfortunately the total length of all selections in a validation list is limited to 256 characters, which makes it quite unsuitable for many tasks. In fact, however, the validation list will display many more than just 256 characters, but the list itself can't be accessed, for example, to take action depending upon which item was selected or to ascertain that an item from the list was selected at all. This obvious short-coming has lead to the development of the system here about to be introduced.

This system works with an MSForms.ComboBox. There is only one such box per worksheet. When a cell is selected which is programmed to have a selection list the combobox will be displayed over the selected cell, assuming its size and properties. It is also connected to the selection list specified for that particular cell or column - there can be many different lists - and linked to the cell it is hiding beneath itself. The user makes a selection which appears in the cell while the combobox itself disappears into hiding until it is called again.

In order to implement this system a worksheet is required which stores the selection lists. This sheet is VeryHidden, meaning the user can't access it. Each list takes up one column. The lists can be created dynamic, so that there are not only different lists to choose from but also updated versions of each, for example, a list of all worksheets in the workbook, or all items currently in the store, or a unique list of all callers during a certain period. This program doesn't deal with the way the lists are created. It just manages their use by the combobox on the worksheet.

You can format the columns and rows in the Lists sheet as you wish. There are, however, a few rules you need to observe in order to ensure that your lists can work with the code. The tab must be named Lists, the first row used for lists must be the same in all columns, and it must be specified in the Enum Nsl in the code module Admin.
Code: Select all
    Enum Nsl                    ' Selection Lists
        NslFirstListRow = 3     ' change as required (see Lists!1:1)
In the attached workbook the specified first row is 3 which is the value assigned to the enumeration NslFirstListRow. You can change it here. It is recommended to have a word like "Select" or "Please select" in the first position of the list because the program will display the first list item by default.

There are lots of useful hints, recommendations, facts and instructions throughout the code, especially in the module Admin and the Test 1 code sheet, which you should read before deploying the code. The system doesn't require you to be proficient with VBA. The module Admin and the Test 1 code sheet are designed for your attention, however. Hopefully, you will never need to look at the others.

After creating the Lists tab, with at least one list in it, in your workbook you can start deploying the code. Start by dragging the module Admin into your VB project. If you already have a module of similar functionality you can also copy the items to your existing module. This is true for all code modules in the attached file. Their names are not significant to the functioning of the code. However, the code does need the Public Constant Hiddensheets. All sheets listed here will be VeryHidden at start. You can add more sheets to be treated the same way, for example the sheet MsgLib if you have deployed my Message system. Just add the name to the constant HiddenSheets, not forgetting a comma to separate the names.

The Enum Nsl must give meaningful names to each of your lists. You could just call them NslList1, NslList2 etc. But that isn't particularly meaningful. The enumerations NslAlpha and NslNumeric, pointing at columns 1 and 2, that is A and B, are unlikely to be part of your own project. Replace them with enumerations of your own lists.

There are 3 procedures in the Admin module. You can run any of them by placing the cursor anywhere within them and pressing F5.
  • SuspendEvents
    This will be useful if you wish to work on your worksheet without the combobox popping up all the time.
  • ResetApplication
    To reverse the effect of suspending application events
  • ToggleVisibility
    This is the procedure the worksheet button "Unhide List" is calling. Chances are that your project may not have such a button. In that case you can use this procedure to make the Lists tab visible or hide it again. The procedure toggles between the two states.

Next, drag the Utilities module into your project. It has the procedures SetApplication, SheetVisibility and LastRow which are used by the code. The LastRow function, in particular, is useful in any project. You can use this function in other code you may have in your project, but be sure that you don't have another function by the same name. the function ColumnNumber is used by both, LastRow and other code in the project.

Drag the code module ListMan into your project. Dragging of code modules creates copies, and can be done in the VB editor's Project Explorer window. The event procedures in the class modules can't be dragged. So, please use copy/Paste.

Copy all the procedures from the ThisWorkbook module to the module by the same name in your own project. Then do the same with the procedures in code sheet for Sheet1. You can open that code module by clicking on it in the VBE's Project Explorer window or by right-clicking the tab Test 1 and selecting 'View Code'. This code must be pasted into the code sheet of every tab on which you would like to have a combobox. The code is the same, but it must be customized. There are detailed instructions how to do that on the code sheet itself. Basically, an association must be created between a column in your worksheet and a list in your Lists sheet, so that the desired selection list will be shown when the combobox appears in a designated column. It is all very simple.

Look at the procedure CbxSelect_KeyDown. You may not wish the cursor to advance to the next row when you press Enter. I often want it to advance to the right, same as when I press Tab. This behaviour can be predetermined in this procedure.
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 32 guests