Excel Data Validation Troubleshooting

How many data validation rules do you have in the giant Excel workbook that you use every morning? If you inherited that file from somebody else, you might not have any idea what’s on some of the sheets. Your workbook could be slowing down, or maybe it’s even crashing, and you’re not sure why. Use these macros for data validation troubleshooting, and see if you need to clean things up.

Continue reading “Excel Data Validation Troubleshooting”

Hide Used Items in Drop Down List

Don’t let Excel yell at us, if we accidentally enter a duplicate value in a column. Okay, maybe Excel isn’t really yelling, but it seems like that when a data validation error message pops onto the screen. Instead of letting people pick a duplicate, use this trick to hide used items in a worksheet drop down list. You can’t pick an item if it isn’t there!

Continue reading “Hide Used Items in Drop Down List”

Dependent Combo Box in Excel

You can add a drop down list in a worksheet cell, so people can select from a list of valid entries. The font in that list is pretty small though, even if the worksheet is at 100% zoom. And good luck trying to read it, if you go to a lower zoom setting! My eyes aren’t that good, so I use a pop-up combo box, in some workbooks, to make the list easier to use.

Continue reading “Dependent Combo Box in Excel”

Data Validation Multiple Selection Edit

If you select an item from a data validation drop down list, it’s entered in the cell, replacing any existing data in the cell. If you’d like to add more items to the cell, instead of replacing the current text, you can use a bit of programming, to allow multiple selections from a data validation list.

In my sample file that shows how to do this, I’ve added new code, to prevent a problem that occurs in one type of situation.

Continue reading “Data Validation Multiple Selection Edit”

Drop Down Hours and Minutes

Last week, someone asked me how to create drop down lists for hours and minutes, and also control the total time that was entered. Production time and Defect time would be entered, and Defect total time could not be greater than the Production total time.

He sent a sample file, with drop down lists in place, but they allowed invalid times to be entered. Was there any way to make it work?

Continue reading “Drop Down Hours and Minutes”

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.

________________________