In this month's Office Q&A, we offer two list solutions that offer dynamic capabilities when combined with Excel's Table object.
Lists compile related items. In Excel, a list is a single column of related values. Excel 2003 introduced the List object, which added a dynamic capability that eliminated a lot of work and the need for some rather specialized knowledge. Excel 2007 introduced the Table object, which expands on the List object's functionality. In this article, I show two readers how to simplify list requirements. First, we combine Data Validation with a Table to limit input in an expanding list. Second, we use VBA code to set conditional limits to storing values in an expanding list.
I'm using Excel 2016 on a Windows 10 system, but the demonstration .xlsm file will run in all Ribbon versions. There's no Excel 2003 counterpart but you can modify both solutions to use Excel 2003's List object instead of a Table.
Data Validation for easy selection
Abdul wants to enter a list of values in a cell and then, upon clicking the cell, be allowed to choose a value from the cell contents. Unfortunately, doing so is a bad idea and unnecessarily complicates data input. When you want to offer or limit input vales, consider using Excel's Data Validation feature. We'll use this feature to insert a list control.
Whether offering or limiting a user's choices, the first thing you need is a source list. You can enter a source list anywhere or even create one from existing data. Since dynamic is always the most flexible, we'll use a Table object (new to Excel 2007).
Figure A shows a simple list converted to a Table as follows:
- Click anywhere inside the list and click the Insert tab.
- In the Tables group, click Table.
- In the resulting dialog, check the My Table Has Headers option, select the range ($B3:$B$7).
- Click OK.
Start with a list of input values.
With the list in place, add the Data Validation list as follows:
- Click the cell where you want to add the validation list.
- Click the Data tab.
- Click Data Validation in the Data Tools group.
- In the resulting dialog, choose List from the Allow dropdown; using the Source control, select B4:B7 or enter the range manually, as shown in Figure B.
- Click OK and Excel displays the dropdown shown in Figure C.
Specify the list range.
To use the list, select a value. How you use that value is up to you.
Because the source list is a Table, you can update the validation list by updating the Table. As you can see in Figure D, Excel adds the new value (Mark) to the validation list. In addition, if your validation control is in a Table, Excel will extend the list control to all new records. There's a lot more to Excel's Data Validation feature; be sure to explore it further on your own.
Quickly modify the validation list by adding a new value to the Table.
If you're using Excel 2003, you'll need a dynamic range for the source list. For details on how it's done, read Pro tip: Create a dynamic validation control in Excel. If you're still using Excel 2003, the Table object is the best reason I can think of to encourage you to upgrade. While Tables have a few limitations, you can't beat the convenience. This solution takes only a few minutes!
Limiting list input via a User Form
Rahul wants to use an Excel User Form to limit data entry. Specifically, he wants to store the value at the sheet level conditionally:
- If the value already exists in the data set, reject the value.
- If the value doesn't exist, store it.
Because the example User Form requires VBA, you'll need a macro-enabled file. The demonstration file is an .xlsm file. If you're using a file of your own, save it as a macro-enabled file before you continue. I'm going to expand Table1 from the earlier solution. You can use any list you like, but it needs to be a Table object.
First, generate the User Form as follows:
- Launch the Visual Basic Editor (VBE) by pressing [Alt]+[F11] or clicking Visual Basic in the Code group on the Developer tab.
- In the VBE, choose User Form from the Insert menu.
- Using the Toolbox, insert a text box and command button (Figure E). We'll use the default names to reduce the number of steps. When working with real-world solutions, consider giving the controls meaningful names.
- Double-click the User Form to launch its code module.
- Enter the procedure shown in Listing A.
- Insert a module from the Insert menu and enter the procedure in Listing B.
Add a few controls to a User Form.
Private Sub CommandButton1_Click() 'Enter new values into ListPerson column in Table1. Dim str As String Dim dbl As Double Dim rng As Range Dim oNewRow As ListRow str = Trim(TextBox1.Value) Set rng = ThisWorkbook.Worksheets("DataValidation").Range("Table1[ListPeople]") 'Handle Match error when match not found. If Not IsError(Application.Match(str, rng, 0)) Then dbl = Application.Match(str, rng, 0) Else dbl = 0 End If 'If match is found, display duplicate error. 'If match found, add new record to Table1. If dbl <> 0 Then MsgBox "You can't enter duplicate values.", vbOKOnly, "Error" Exit Sub Else 'Select Table1. rng.Select Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True) oNewRow.Range.Cells().Value = str Set oNewRow = Nothing End If End Sub
Sub Button2_Click() 'Show Userform1 UserForm1.Show End Sub
Return to the sheet and add the procedure from Listing B to the Quick Access Toolbar QAT as a macro button as follows:
- Choose More Commands from the QAT dropdown.
- From the Choose Commands From dropdown, select Macro.
- Select Button2_Click and click Add (Figure F).
Add the procedure to the QAT.
If you don't want the macro in the QAT (and this one is specific enough that you might not want it there), embed a command button in the sheet.
With all the pieces in place, you're ready to run the User Form. To do so, click the new macro button to display the User Form. Enter a name that isn't in the list, such as Tom, and click the Enter Value command button. The Match() function in the code doesn't find a match, so the first If executes its Else clause and sets dbl to 0. The second If also executes its Else clause, which adds a new row to Table1 and enters the value you entered, as shown in Figure G. Close the User Form.
Add a new name to the list using the User Form.
Let's try it again; this time, enter a name that's in the list. When you click the Enter Value command button, the Match() function finds a match and sets dbl to equal its result. The second If then displays the message box shown in Figure H. Clear it and close the User Form.
The code rejects the duplicate entry.
This code offers a good beginning, but it needs more polish. First, there's no error-handling, so you'd want to customize it accordingly. A few notes about Match() are also in order:
- MATCH() isn't case-sensitive, so text values might need additional checks if that's important.
- If you accidentally include a space before or after a value that otherwise already exists, Match() won't see the two values as a match. Including a TRIM() wrap prevents that, but you might find you need more control than TRIM() alone.
Depending on how you use the User Form, you might want to clear the text box and allow continuous data entry without closing the User Form. Keep in mind that anytime you allow users to manually enter values, typos are possible.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.
- Office 2016: A roundup of the best new capabilities
- Eight tips for working more efficiently in the Visual Basic Editor
- Office Q&A: A SUMIFS() matrix and absolute Table referencing
- Use PowerPoint's push transition to connect related slides