Displaying Error Messages in Excel

This tutorials follows on from the previous page

In the previous part, you created drop down lists so that you can simply select the data you want, rather than typing it in all the time. In this part, we'll display error messages if a user types in too much data.

Displaying Error Messages

We can add Validation to the Comments field in our spreadsheet. We'll restrict the amount of text that can go in the Comments field to a maximum of 25 characters. The comments field you should have is this one:

So highlight the Comments column and bring up the Data Validation dialogue box again (Click Data > Validation from the menu). This time, in the Allow drop down box select "Text Length". A few more fields will appear on the dialogue box:

The Between in the Data text box is exactly what we're looking for. But we need to enter values for the Minimum and Maximum fields. These are the Minimum and Maximum text lengths that can put in any cell in the comments column. We'll restrict the length to 25 characters, just so you can see how it works.

We can add an error message, too, so that we can tell users what they did wrong. To add an error message, do the following:

If your dialogue box doesn't look like the one above, make sure there is a tick in the box at the top "Show error alert after invalid data is entered."

There are three different Styles you can choose from for your error message. Click the black down arrow just below Style to see them. Click on each one in turn and see what happens. Then set it back to Stop.

Click OK when you've finished. To test it out, click inside cell E2 and type the following: Steven can do a lot better than this. Then press the return key on your keyboard. Your error message should pop up and look like this one:

The error alert gives the user the changes to either Cancel the data already input, or to Retry.

The only thing spoiling the look of our spreadsheet are the cells starting at F2, the ones from our list. We can hide all that data from prying eyes.

Hiding Data in a Spreadsheet

To hide data on a spreadsheet, do the following:

Now try this. Click anywhere on the D column. Click Format > Column > Unhide. What happens?

Nothing happened, right? So why didn't Excel Unhide your columns?

It's because you did not tell Excel which columns you wanted to Unhide. To do that, highlight columns E and I. Then click Format > Column > Unhide. Your data should return.

If you don't want anyone else to Unhide your data, you can Protect the worksheet from unwanted changes. Just click on Tools > Protection > Protect Worksheet. A dialogue box appears. Select your options, and then click OK.

And that's it for our brief look at Forms. You can do a whole lot more with Forms, but that enough for us. We can move on to Web Integration.

Move on to Web Integration -->

<-- Back to the Excel Contents Page