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.

• So click inside the Minimum text box
• Enter the number 1
• Click inside the Maximum text box
• Enter the number 25
• Don't click OK just yet

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:

• Click on the Error Alert tab strip of the Data Validation dialogue box
• The dialogue box will change to this:

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 inside the Title text box and type "Too many characters"
• Click inside the Error message text area and type "The maximum number of characters for this field is 25 - please try again"
• Your dialogue box will then look like this:

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:

• Highlight the data you want to hide (in our case, highlight F2 to H9)
• From the Excel menu bar, click on Format
• From the drop down menu, select Column
• From the sub menu that appears, click Hide
• The three columns with the list data in them will disappear

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.