Macros for validating data in excel Random chat cums

Posted by / 01-Feb-2018 21:11

The only possible way around this is to use a macro to head off pasting into certain areas of the worksheet.

A good way to do that is found at John Walkenbach's site: is your source for cost-effective Microsoft Excel training.

Draw-Back However, when we do the above we must either scroll to the needed item in the Validation list, or type the whole word.

What would be really nice is if Excel would Auto Complete based on our list!

Allen Wyatt With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf.

He is president of Sharon Parq Associates, a computer and publishing services company. Check out To force the user to allow macros to run, I hid all the tabs except one which I call "Welcome", which just has a message that the workbook needs macros enabled to run.

You can work with any simple list or download the demonstration file.

Melissa has discovered what some have called the "Achilles' heel" of Data Validation—you can paste over cell contents and thereby destroy whatever Data Validation rules were in the target cells.

You can hide the temporary value by just changing the color to white.

Excel's data validation feature lets you embed a drop-down list into a sheet.

You can populate that list in many ways, but a static list is probably the easiest.

If you update the list frequently, the feature's list is inadequate.

macros for validating data in excel-85macros for validating data in excel-34macros for validating data in excel-46

Melissa uses Data Validation regularly to limit what can be put into a cell.