Answers to frequently asked questions about the Data Validation Multi-Select Premium (Data Entry Popup) product 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 DVMSP 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. The steps below show how to:
If you leave the on/off setting cell blank, the popup list might not run correctly, because it looks for a range named "0" (zero), which does not exist.
To fix this problem, follow these steps:
If Me.Range("0").Value <> "" Then GoTo exitHandler
If you want to move the on/off cell to a different worksheet, follow these steps:
If Me.Range("J3").Value <> "" Then GoTo exitHandler
If Sheets("Admin").Range("A2").Value <> "" Then GoTo exitHandler
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.
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.
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.
DVMSP Kit Workbooks: The workbooks in the Data Validation Multi Select Premium (DVMSP) kit are licensed for one user, so files in the kit cannot be shared with others. You can build an unlimited number of Excel workbooks with the kit that you purchased.
Workbooks You Build With the Kit: If you use the DVMSP kit to add the popup list to other workbooks, you can freely share those workbooks that you created. Other people will not need the DVMSP kit, in order to use the completed workbooks that you built with your licensed copy.
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:
By default, when you click the Cancel button, the cell below is selected. You can change that setting in the popup form's code
In the DVMSP Setup file, you can turn the Form Positioning on or off, in the Customize the Code - Advanced step.
If the Popup List opens with its title bar off the screen, follow these steps to move it:
NOTE: You can also press the Esc key to close the Popup Form.
If the Popup List is not appearing in the correct position, you can change the Form Positioning to "No", by following these steps:
If that doesn't fix the problem, there is also Form Positioning code in the Initialize procedure for the frmDVList. You can make an additional change to the code there.
Here is the default code, which puts the top left corner of the form in the center of the Excel window, if form position is "Yes". For "No", it tries to center the form in the Excel window
If bPos = True Then Me.StartUpPosition = 0 Me.Top = ActiveWindow.Top _ + ActiveWindow.Height / 2 _ - Me.Height / 2 Me.Left = ActiveWindow.Left _ + ActiveWindow.Width / 2 _ - Me.Width / 2 Else Me.StartUpPosition = 1 'center owner (center in Excel window) End If
If the StartUpPosition 1 isn't working, try one of the other options that Excel offers
Me.StartUpPosition = 2 'center on the screen OR Me.StartUpPosition = 3 'upper-left corner of screen
After you add the popup list to your workbook, you can adjust the popup form, to make it wider.
To see the UserForm that contains the popup list:
To make the UserForm and list wider,
Then, click the Save button, and close the Visual Basic Explorer, to go back to Excel.
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.
NOTE: Select a Separator that is not included in the items of your list. For example, if your items contain a comma or a hyphen, do not use those as separators.
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.
Follow these steps to make the changes:
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.
Private Sub UserForm_Initialize()
Me.lstDV.Enabled = False Application.Wait (Now + TimeValue("00:00:01")) Me.lstDV.Enabled = True
The form has an ALL button that will select all the items in the list. If you want to hide the ALL button for single selection columns, follow the steps below:
Find the code:
Add a new line of code:
Save 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 DVMSP Setup file, and then add the code to the workbook again.
If you want to make changes to your DVMSP 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-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.
If Excel hangs or crashes when the popup list opens, the problem is usually caused by the named range where your list of items is stored. If that named range includes an entire column, that's over a million rows, and the popup list can't process that many items, so it hangs or crashes.
If possible, keep your list in a named Excel table, and the list will expand or shrink automatically, if you add or remove items. You won't need to include any blank rows at the end of the list, for future additions.
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.
If you have any other questions about the Data Validation Multi-Select Premium tool, please send them to me by email:
ddalgleish @ contextures.com
Last updated: April 9, 2019 9:53 AM