Contextures

DVMSP Data Entry Popup FAQ

Answers to frequently asked questions about the Data Validation Multi-Select Premium (Data Entry Popup) product from Contextures.

Code Doesn't Run

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

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.

dvmsp on off cell

If you leave that cell blank, the Listbox code might not run correctly, because it looks for a range named "0", which does not exist.

dvmsp on off cell

To fix this problem, follow these steps:

  1. Right-click the sheet tab, and click View Code
  2. In the code, look for this line:
    • If Me.Range("0").Value <> "" Then GoTo exitHandler
  3. Then, do either of these things:
    • Type an apostrophe at the start of the line, to change it to a comment, which will not run as a line of code

dvmsp on off cell apostrophe

  • OR, replace the "0" with a cell address, such as "J3" -- then, you can use that cell as your on/off switch.

dvmsp on off cell cell address

Error - Variable Not Defined

When you set up the data validation 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.

dvmsp on off cell cell address

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.

    dvmsp on off cell cell address

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

    dvmsp on off cell cell address

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

    dvmsp on off cell cell address

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

In the screen shot below, you can see the Month column's data validation list, which is based on the named range, MonthList.

data validation list

In the next screen shot, the Name Manager (on the Ribbon, Formulas tab) is open, and the MonthList is highlighted.

named range

Sharing the Workbooks

The workbooks in the Data Validation Multi Select Premium (DVMSP) kit are licensed for one user, so those workbooks cannot be shared with others.

However, you can share any workbooks that you create, in which you have inserted the DVMSP code and userforms. Other users will be able to use those workbooks, like any other Excel file -- they do not need the DVMSP kit in order to use the completed workbooks that you built with your licensed copy.

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. In Excel, press Alt+F11 to open the Visual Basic Explorer (VBE)
  2. At the left, find your workbook, and click the + symbol to see the Excel Objects and Forms
  3. Click the + at the left of the Forms folder
  4. Right-click on frmDVList, and click View Object
  5. Right-click on the listbox (the big white rectangle), and click View Code
  6. Type an apostrophe at the start of the line: "cmdOK_Click". That will change the code to a comment, so it won't run.
  7. Click the Save button, at the top left of the VBE window.
  8. Close the VBE window.

data validation list

Separators

In the DVMSP_Setup file, there are several built-in options for separators, and you can select any of those. 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.

data validation list

Double-Click to Open the List

The kit is designed to open the list automatically, when you select a cell that has a data validation drop down list. Because the worksheet code runs every time a cell is selected, that can slow down a large, complex workbook.

To speed things up, you can make a couple of changes to the code, so it only runs when you double-click on a cell that has a data validation drop down list.

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

worksheet code selectionchange

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

worksheet code beforedoubleclick

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

worksheet code delete 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)
  • Then, click the Save button, at the top right of the Visual Basic Editor (VBE) window.
  • Close the VBE window

worksheet code cancel true

(Optional) Add Delay to UserForm Code

Using a double-click can result in one or two items being accidentally selected when the list opens. This occurs if the popup list opens over the cell that was double-clicked.

To prevent this from happening, follow these steps to add delay code to the UserForm that contains the popup list. This locks the list for one second when the form opens, so items are not accidentally clicked.

  1. In Excel, press Alt+F11 to open the Visual Basic Explorer (VBE)
  2. At the left, find your workbook, and click the + symbol to see the Excel Objects and Forms
  3. Click the + at the left of the Forms folder
  4. Right-click on frmDVList, and click View Code
  5. Scroll down about halfway, to find the Initialize section:
    • Private Sub UserForm_Initialize()
  6. Scroll down, past the list of "Dim" statements, and the"On Error Resume Next" line
  7. Click in the blank line just below that, and press the Enter key, to start a new line.
  8. Copy the following code, and paste it into the new line. This code locks the list, waits one second, then unlocks the list. This is the shortest delay time possible.
    • Me.lstDV.Enabled = False
      Application.Wait (Now + TimeValue("00:00:01"))
      Me.lstDV.Enabled = True
  9. Click the Save button, at the top left of the VBE window.
  10. 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 DVMSP Setup file, and then add the code to the workbook again.

Compile error: Ambiguous name detected

Avoid the Problem

If you want to make changes to your DVMSP 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 DVMSP kit adds "Option Explicit" at the top, and a procedure named Worksheet_SelectionChange. That procedure has a comment at the top, "'show Listbox when a data validation cell is selected "
  3. If that's all that is 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 DVMSP 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 DVMSP 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 DVMSP code, press Ctrl + A, to select everything, then press the Delete key to clear the code module. If there is non-DVMSP code on the sheet, see the section below.
  6. Then use the DVMSP 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-DVMSP code in it, you would need to combine both the DVMSP code and the non-DVMSP 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 DVMSP 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.

More Questions

If you have any other questions about the Data Validation Multi-Select Premium tool, please send them to me by email:

ddalgleish @ contextures.com

Related Tutorials

Data Validation Multi-Select Premium

Data Validation Basics

Create Dependent Lists

Data Validation Tips

Search Contextures Sites

 

pivot power premium

 

 

Excel UserForms for Data Entry

 

 

 

 

Last updated: September 25, 2016 9:47 AM