DESP FAQ

Answers to frequently asked questions about the Data Entry Search Popup (DESP) kit from Contextures.

Code Doesn't Run

There are a few reasons why the code might not run:

• Macros are not enabled. To enable them, follow the instructions here: Enable Macros in a workbook
• The On/Off Cell was not set up. Read the instructions below, to fix the problem.
• The data validation drop down lists might not be set up correctly

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/Off Cell

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.

Error - Variable Not Defined

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,

• Click OK, to close the error message
• In the Visual Basic Editor, click the Run menu
• Click the Reset button, to stop the code from tring to run.

  

• Click the Tools menu, then click the References command.

  

• In the References window, scroll down to find Microsoft Forms 2.0 Object Library, and add a check mark to it.
• Click OK, to close the References window.

  

• Close the VBA window
• Save the Excel file, then close and re-open Excel, and open the file
• The code should work correctly, without showing the error message.

Data Validation Settings

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.

Sharing the Workbooks

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.

List Closes After Selection

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:

1. On the sheet where you pasted the code, right-click the sheet tab, and click View Code.
2. Scroll down a bit, to the "Auto Close Popup After Single Selection?" comment
3. In the next line, change bClose to False
4. Click the Save button, at the top left of the VBE window.
5. Close the VBE window.

Separators

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.

Double-Click to Open the List

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.

Change the worksheet code

(Optional) Add Delay to UserForm code

Change the Worksheet Code

Follow these steps to make the changes:

• On the sheet where you pasted the code, right-click the sheet tab, and click View Code.
• At the top of the code module, click in the blank row above the Private Sub Worksheet_SelectionChange line

• At the top right of the window, click the arrow in the Procedures drop down, and click on BeforeDoubleClick

• Select from the End Sub line, down to the Private Sub Worksheet_SelectionChange line
• Delete those lines

• Scroll down a bit, to the first "Select Case Target.Column" section
• Click in the blank line above that section
• In the blank line, type: Cancel = True (that turns off the default double-click action, such as entering the cell to edit it)
• Change the bDblC variable from False to True
• Then, click the Save button, at the top right of the Visual Basic Editor (VBE) window.
• Close the VBE window

Compile Error - Ambiguous Name

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.

Avoid the Problem

If you want to make changes to your DESP setup options, delete the old code from the data entry sheet first.

1. To see the code, right-click on the sheet tab of your data entry sheet, and click View Code.
2. Scroll through the code, to see what is there. The DESP kit adds "Option Explicit" at the top, and a procedure named Worksheet_SelectionChange. (If you selected the Double Click option, you will see a procedure named Worksheet_BeforeDoubleClick).
• That procedure has a comment at the top, "'show Listbox when a data validation cell is selected "
3. If that is the only procedure on the code module, press Ctrl + A to select everything, then press the Delete key to clear the sheet.
4. If there are other procedures on the code module, do not delete those.
5. Then, go to the DESP Setup file, make the changes to your customization options, and click the button to copy the code into the new workbook.

Fix the Problem

If there are duplicate procedures on your data entry sheet, follow these steps to try to fix the problem.

1. To see the code, right-click on the sheet tab of your data entry sheet, and click View Code.
2. Scroll through the code on the sheet, to find the two procedures that are named Worksheet_SelectionChange.
3. Check the code in each procedure, to see if it is from the DESP kit -- that procedure has a comment at the top, "'show Listbox when a data validation cell is selected "
4. Before you change the code, make a backup copy of your file.
5. If both copies of the Worksheet_SelectionChange procedure are copies of the DESP code, press Ctrl + A, to select everything, then press the Delete key to clear the code module. If there is non-DESP code on the sheet, see the section below.
6. Then use the DESP Setup file to customize the code, and to install it again.

After you delete the duplicate procedure, things should work again, without showing an Ambiguous name detected error message.

Combine With Existing Code

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.

No Items in Popup List

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.

Change Popup List Size

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:

• Press Alt + F11 to open the Visual Basic Editor (VBE)
• At the left, in the Project Explorer, click the + at the left of the Forms folder
• Double-click on the UserForm named frmDESPList

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:

1. To adjust the size of the UserForm, click on its border, to select it
2. Point to the handle at the center right, and drag to the right, to make it wider
3. Next, click to the right of the Selected tab, to select the tab control
4. Point to the handle at the center right, and drag to the right, to make it wider
5. Click the List tab, then click on the ListBox on that tab, and drag to make it wider
6. Do the same on the Selected tab
7. Optional -- Move the buttons, to adjust their location on the wider form
8. Click the Save button, to save your changes.

To make the lists taller, follow these steps:

1. To adjust the size of the UserForm, click on its border, to select it
2. Point to the handle at the bottom center, and drag down, to make it taller
3. Move the buttons down, to make room for the taller lists
4. Next, click to the right of the Selected tab, to select the tab control
5. Point to the handle at the center right, and drag down, to make it taller
6. Click the List tab, then click on the ListBox on that tab, and drag down, to make it taller
7. Do the same on the Selected tab
8. Click the Save button, to save your changes.

More Questions

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

Related Pages

Data Entry Search Popup

Data Validation Basics

Create Dependent Lists

Data Validation Tips