When you set up a worksheet for other people to use, data validation messages can help them get started. The messages appear, like little ToolTips, when a user clicks on a cell. After using the workbook for a while, users might not need those messages anymore, and they become annoying, rather than helpful.

AlexJ, who recently shared his technique for hiding rows with Excel outlining, has created another useful sample. In this file, he lets users turn those data validation messages on or off, by choosing TRUE or FALSE from a drop down list.

When TRUE is selected, the label cells, such as Name, are green. Click on a label cell and a data validation input message appears, with instructions for that field.

Select FALSE and the label cells turn white, and no data validation input message appears when you click on a label cell.

How It Works

The TRUE/FALSE drop down list is in a cell named ShowUserMsg, and the list is created with data validation.

The labels cells also have data validation, which is set to allow Any value.

For each label, an Input Message is entered in the Data Validation dialog box.

The label cells and ShowUserMsg cell are coloured with Conditional Formatting.

Code to Show or Hide the Messages

When you select an option from the TRUE/FALSE list, a Worksheet_Change event runs, and turns the messages on or off. To see the code in Alex’s sample file, right-click the Show User Messages sheet tab, and click on View Code.

Download the Sample File

You can download the Show or Hide Messages file here, or from the Sample Spreadsheets page on the Contextures website.

___________________