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)
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.