Answers to frequently asked questions about the Data Entry Search Popup (DESP) kit from Contextures.
There are a few reasons why the code might not run:
NOTE: Be sure that your workbook shows letters in the column headings, and not numbers.
The DESP kit is designed to run in "A1" reference style (letters). In R1C1 reference style (numbers in column headings), list items might not appear, for lists based on a formula, such as INDIRECT.
On the Customize sheet in the Setup file, you can enter a cell address, to use as an on/off switch, to prevent the macro from running occasionally. For example, you might want to edit the worksheet, without the listbox appearing.
If that on/off cell is filled, the code will not run. Clear the cell to enable the popup list again.
When you set up the DESP listbox in your own workbook, or in the Practice file, you might see an error, when you click on a data validation cell. "Compile Error: Variable not defined."
In the background, the VBA code is showing, and a word is highlighted.
To fix the problem,
Before you add the data validation multi-select code, you must add drop down lists in the cells on the data entry worksheet. The lists must be based on named ranges, which are Workbook level.
Also, the source lists must be vertical (down a column), and not horizontal.
In the screen shot below, you can see the Month column's data validation list, which is based on the named range, MonthList.
In the next screen shot, the Name Manager (on the Ribbon, Formulas tab) is open, and the MonthList is highlighted.
You can freely share any workbooks that you create from the DESP kit, in which you have inserted the DESP code and userforms. Other users will be able to use those workbooks, just like any other Excel file. They do not need the DESP kit in order to use the completed workbooks that you built with your licensed copy.
However, the Setup file, and the other workbooks in the Data Entry Search Popup (DESP) kit are licensed for one user, so those workbooks cannot be shared with others.
In columns that are set up for single selection, the list will close automatically, after you make a selection. If you would prefer to keep the list open, follow these steps to change that setting:
In the DESP_Setup file, there are several built-in options for separators, and you can select any of those. Be sure to use a separator that is not used in your list items.
Also, there is a sheet with a list of separators (Lists_Sep), where you can change any of the existing options, or add new options. The range of cells with the thick border is a named range, so be sure to insert your new item within that range.
In the Setup file, you had the option to show the popup list on a single click or a double click. If you want to change from single click to double click later, you can edit the code.
Follow these steps to make the changes:
The kit is designed for workbooks that do not have any existing macros on the worksheet. If you add the code to a worksheet that already has a Worksheet_SelectionChange procedure, an error message will appear when you click any cell on the worksheet.
Compile error: Ambiguous name detected: Worksheet_SelectionChange
This can happen if you change the setup options in the DESP Setup file, and then add the code to the workbook again.
If you want to make changes to your DESP setup options, delete the old code from the data entry sheet first.
If there are duplicate procedures on your data entry sheet, follow these steps to try to fix the problem.
If the other copy of the Worksheet_SelectionChange procedure has non-DESP code in it, you would need to combine both the DESP code and the non-DESP code into one Worksheet_SelectionChange procedure.
This kit does not include assistance for modifying your existing code. You will need programming skills, or help from a programmer, if you plan to integrate the DESP code with existing code on the data entry sheet.
If the popup list does not show any items, it's usually because the macro can't understand the name that was used for the cell's drop down list.
This can occur if the cell uses the INDIRECT function to create a dependent list. This feature works when the column headings are letters (A1 ref style), but does not work if the column headings are numbers (R1C1 ref style). Be sure to use the A1 Ref Style when using the INDIRECT function for your drop down lists, to avoid this problem.
The popup list is contained in a UserForm, and you can adjust its width and height. Do this in the Setup file, if you want to change the master UserForm, or make the changes in another workbook, after you have added the DESP forms.
To see the UserForm:
In the UserForm, there are two tabs -- List and Selected. On each of those tabs, there is a ListBox where the popup list items are displayed.
To make the lists wider, follow these steps:
To make the lists taller, follow these steps:
If you have any other questions about the Data Entry Search Popup tool, please send them to me by email:
Debra Dalgleish: ddalgleish @ contextures.com
Last updated: March 4, 2021 3:35 PM