Worksheet Combo Box Problem in Excel 2013

worksheet combo box listfillrange http://blog.contextures.com/

On of my favourite Excel tricks is to show a combo box, when you click a cell that has a drop down list. The combo box can have larger font, and it autocompletes, so it’s easier for people to use.

There is only one ActiveX combo box on the worksheet, and it is hidden until you click on a drop down cell. When it appears, it shows the data validation list for the selected cell.

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Worksheet Combo Box Problem in Excel 2013

Clear Dependent Drop Down Cells

clear dependent cells blog.contextures.com

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.

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

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Clear Dependent Drop Down Cells

Add New Items to Excel Drop Down List

add new item to 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

Show Data Validation Items in List Box

list box for data validation

In February, I shared a sample file that shows a popup list box, when you click on a cell that has a data validation list. The list box shows all the items from the source list, with check boxes, so you can select multiple items.

Continue reading Show Data Validation Items in List Box

Adding Form Controls on a Worksheet

Excel drop down lists

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.

This week, I was happy to spend some time on Skype, talking about Excel with my friend Chandoo. While he sweltered in India’s heat, I shivered in our Canadian snow, so it averaged out the perfect temperature for a friendly, and informative, discussion. And you'll be able to hear what we said -- Chandoo has posted a recording in the next episode of his Excel

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.

Long ago, while we were all still using Excel 2003, Roger Govier shared his technique for creating dependent drop down lists using Index.

There is one big lookup table, with Regions listed in the first column, and the remaining columns have the lists for each region and country.

The INDEX function created the applicable drop

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. For example, you can use an OFFSET formula, which counts the entries in the column. The count is used to set the number of rows in the range.

When there are blanks, as in the screen shot below, the range is incorrect. There are 9 items, so 9 rows are included, but the last item is in the 12th row of the list.

If you create a drop down list based on this range, it includes blanks, and August is the last

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 I’ve created a new variation on this, combining it with the combobox popup technique from one of my other data validation examples. In the screen shot below, there is a combo box over cell B3, with a list of Weekdays. There is one hidden combo box on

Continue reading Select Cell Items From ListBox

Multiple Selection Drop Down With Codes

There is a sample file on my website that has VBA code for selecting multiple items from a data validation drop down list. You can insert all the selected items into the active cell, or down the adjacent column, or across a row.

There is a video at the end of this article, that shows how the technique works.

Add a Matching Code in Adjacent Cell

Last week, someone asked it it was possible to put a code for each selection, in the cell to the right. For example, if you select a product in cell C3, put the

Continue reading Multiple Selection Drop Down With Codes

Add New Items to Data Validation Combo Box

There are a few versions of the data validation combo box technique on my website. The files use programming to make a combo box appear when you click, or double-click, on a cell that has a drop down list.

The advantage to using a combo box is that you can show the text in a larger font, and it autocompletes as you type, unlike a cell with a drop down list.

Add New Item to Drop Down List

Another one of my sample files let you add new items to a drop down list, on the fly. If you

Continue reading Add New Items to Data Validation Combo Box