Excel Drop Down List Allows Invalid Entries

Drop down lists make it easier to enter data on a worksheet, and they help ensure that only valid items will be entered in the cell.

For example, in the screen shot below, the Manager column has a drop down list with 5 names. Only those names should be entered in the column.

Continue reading "Excel Drop Down List Allows Invalid Entries"

Select Multiple Items From Long List in Excel

It's hard enough to select one item from a long drop down list – how can we make it easier to select multiple items for a cell?

In the screen shot below, the worksheet has code that lets you select multiple item from the drop down list. Each new item is added to the cell, instead of replacing the previous selection.

datavalmultiselectproducts01

Use a ListBox

To see more of the list, and select multiple items at once, you can use a ListBox, embedded in a UserForm. When you click OK, all of the selected items are entered in the cell, separated by commas.

This technique works for drop down lists that are based on a named range, such as MonthList, or AllProducts.

multiselectlistbox03

Really Long List

However, if your list has thousands of items, it can take a while to scroll through a ListBox too. So, to make it easier to check items in the list, I've created a new sample file, based on the previous ListBox file.

  • It has a combo box at the top of the UserForm, and you can start typing an item name there. When the correct item shows up, click Add, or press the Enter key, to add it to the list.
  • You can also use the ListBox, to check or uncheck items.
  • When you've finished selecting items, click OK, to add all the items to the cell.

datavallistboxcombobox01

In the sample file, there are two versions of the technique –

  • one opens the ListBox UserForm when you click on a cell with a drop down list
  • one opens the ListBox UserForm when you double-click on a cell with a drop down list

Download the Sample File

To see how this technique works, you can download the sample file from my Contextures website. On the Sample Files page, go to the Data Validation section, and look for DV0067 - Select Multiple Items in ComboBox or ListBox

The zipped file is in xlsm format, and contains macros. Enable the macros, when you open the file, if you want to test the code.

To see the code, right-click on the DataEntry_Click or DataEntry_DoubleClick sheet tab then click View Code.

________________________

Show Drop Down List With Specific Letters

If you've got a long list of items, it can take a while to find what you're looking for, in a data validation drop down list.

For example, in the screen shot below, you'd have to scroll down to the bottom of this long list, if you want to select the Teatime Chocolate Biscuits (and don't pick the Tofu by mistake!)

Continue reading "Show Drop Down List With Specific Letters"

Clear Dependent Drop Down Cells

You can use data validation to create a drop down list on an Excel worksheet. For example, show a list of countries, if you click a cell in column B.

After you’ve set up that first list, you can make another list, in column C, to show the cities for the selected country.

dependentclear02

We’ll see how to set this up, and prevent problems, by clearing out the city cell, when necessary

dependentclear07

Continue reading "Clear Dependent Drop Down Cells"

Add New Items to Excel Drop Down List

There are a few sample files on my web site that let you automatically add new items to a drop down list. The most recent version works in newer versions of Excel, and uses named tables to store the lists.

When the code detects a new item, it asks if you want to add it to the drop down. If you click Yes, the item is added, and the lookup list is sorted in alphabetical order.

Continue reading "Add New Items to Excel Drop Down List"

Adding Form Controls on a Worksheet

Do you spend your days, and maybe your nights, talking about Excel with your friends? It’s amazing how quickly the time passes, while you chat about all the awesome features and tricks that you know. You know, exciting things like adding form controls on a worksheet.

Continue reading "Adding Form Controls on a Worksheet"

Dependent Drop Down Lists With Tables

With dependent drop down lists, you can control what appears in a drop down, based on what was entered in the previous cell. In this example, you select a region, then a country in that region, then an area, and finally a city. See how to set up dependent drop down lists, with tables that make this easy to maintain.

Continue reading "Dependent Drop Down Lists With Tables"

Dynamic List With Blank Cells

If a list contains blank cells, the usual method for creating a dynamic named range doesn’t work. Usually, you would use an OFFSET formula, and count the entries in the column, to calculate the number of rows in the range. Here is a workaround to create a dynamic list with blank cells.

Continue reading "Dynamic List With Blank Cells"

Select Cell Items From ListBox

I’ve posted a few techniques for selecting multiple items from the drop down list in a cell. In the screen shot below, there is a drop down list in cell C3. When you select an item from the list, it’s added to the cell, instead of overwriting the previously selected value. Now, instead of a cell drop down, here's how to select cell items from ListBox.

Continue reading "Select Cell Items From ListBox"