Sunday, July 24, 2016

Widgets

MS Excel Add Error Messages


HOME



Data Validation – Restricting what data go in a cell.

You can also restrict what goes in to a cell on your spreadsheet, and display an error message for your users.  We will do this with our comments column.  If users enter too much test, we will let them know by displaying a suitable error box.  Try the following:

Highlight the E column on your spreadsheet (the comments column).

From the Data Tools panel, click Data Validation to bring up the dialogue box again.


From the Allow list, select Text length:


When you select Text length from the list, you will see three new areas appear:


What we are trying to do is to restrict the amount of text a user can input into any one cell on the Comments column.  We will restrict the text to between 0 and 25 characters.

The first of the new areas (Data) is exactly what we want – Between.  For the minimum textbox, just type a 0 (Zero) in there.  For the maximum box, type 25.  Your dialogue box should then look like this:


To add an error message, click the Error Alert tab at the top of the Data Validation dialogue box:



Make sure there is a tick in the box for “Show error alert after invalid data is entered.”

You have three different Styles to choose from for your error message.  Click the drop down list to see them:


In the Title tex tbox, type some text for the title of your error message.


Now click inside the error message field and type some text for the main body of your error message.  This will tell the user what he or she did wrong.



Click OK on the Data validation dialogue box when you are done.

To test out your new error message, click inside any cell in your Comments Column.  Type a message longer than 25 characters.  Press the enter key on your keyboard and you should see your error message appear:


As you can see, the user is prompted to Retry or Cancel.  But our title (Too many characters) is at the top, our stop symbol is to the left, and our error message is displaying nicely.

Hiding Spreadsheet Data in Excel 2007 to 2013:

The data that went in to our lists does not need to be on show for all to see.  You can hide this text quite easily.

Highlight the columns with your data in it (F, G, and H for us).
Click on the Home tab from the top of Excel.
Locate the cells panel.
On the cells panel, click on Format.
You will see the following menu:


Move your mouse down to Hide and Unhide and you will see a sub menu appear:


Click on Hide columns from the Sub menu.  Excel will hid the columns you selected:


In the spreadsheet above, the columns F to H are no longer visible.

To get them back again, highlight the columns E and I.  From the same submenu, click Unhide Columns.


HOME


No comments:

Post a Comment

Total Pageviews