Creating Forms with Input Messages and Hyperlinks in Microsoft Excel

Authors of workbooks using the Microsoft® spreadsheet program Excel® can use the Input Message feature of Excel to provide outstanding feedback and instructions for users of JAWS® screen reader software and MAGic® screen magnification software. Hyperlinks can be used to expedite navigation within the form for JAWS and MAGic users.

Excel Versions Prior to 2007

  1. From any cell or selected group of cells, press ALT+D to open the Data menu.
  2. Press DOWN ARROW to move to and select the Validation menu item followed by ENTER. Alternatively, press the access key L to open the Data Validation dialog box from the Data menu. A three-page Data Validation dialog box opens with focus on the Settings page. This dialog box allows you to choose the kinds of messages, titles, prompts, and errors to use, and whether tool tips are shown or not.
  3. Press TAB to move to the Allow combo box for Validation Criteria.

Here you can choose to allow Any Value, Whole Number, Decimal, List, Date, Time, Text Length, or Custom based on formulas. The first choice, Any Value, is used most often for names or general alpha-numeric input. The Time and Date choices force input to be in the form of a valid time or date and offer you two further fields where you can restrict the values to a range of dates or times. The same is true for several of the other choices. For example, if you choose Whole Number you can then restrict input to include the following choices:

The choices available depend on the item selected in the Allow combo box.

Creating a Dropdown List Using the Settings Page

  1. If you are not already there, press TAB to move to the Allow combo box.
  2. Choose "List" in the Allow combo box.
  3. Press TAB to move to the Source edit area. To define the list locally, type the list values separated by commas.

EXERCISE:  For example, you might type the following
 Fall, Winter, Spring, Summer
 to create a list of college semesters.


To use a range of cells with a defined name, type the equal sign (=) followed by the name of the range. To use cell references, select the cells on the worksheet and then press ENTER.

NOTE: If you use the ARROW KEYS in this edit area a selection rectangle moves in the worksheet from one cell to another in the background and the address of that cell shows up in the edit field. This is fine for selecting a list of values either from a single cell or a group of cells, but it may be easier to type in text values, depending on your goal. Just realize that if you are using a keyboard for input, pressing the arrow keys to edit and review the text you type does not work in this field, so exercise caution when typing so as not to use the arrow keys unless that is really what you intend to do.


  1. Press TAB to move to the "In-cell dropdown" check box and make sure it is checked.

Creating an Input Message Using the Input Message Page

  1. Press CTRL+TAB to move to the next page of the multi-page dialog box, the Input Message page.
  2. Press TAB to move to the "Show input message when cell is selected" check box and make sure it is checked. If you uncheck this, JAWS announces "not visible" but the input message is NOT spoken.
  3. Press TAB to move to the next control, the "Title" edit box. The title, if created, becomes part of the message. Creating a title is often redundant, and causes information to be spoken twice, once for the title and once for the message, so the author recommends leaving the title blank.
  4. Press TAB to move to the next control, the "Input message" edit box. Type in an input message. You do not have to type the restrictions or parameters in the message, since JAWS reads those aloud automatically. There is a limit of 255 characters for this multi-line edit field.

EXERCISE:  You should tell JAWS users how to access the dropdown items in your message! For example, your text might say something like this. "Press ALT+DOWN ARROW to open the dropdown list and press ENTER to choose one of the items."

Error handling Using the Error Alert Page

  1. Press CTRL+TAB to move to the next page of the multi-page dialog box, the Error Alert page.
  2. Press TAB to move to the first check box, "Show error alert after invalid data is entered" and make sure it is checked.
  3. Press TAB again to move to the Style combo box. There are several choices here, Stop, Warning, and Information.

Stop Error

The Stop error choice does not allow any other input than what was set in the validation criteria established previously. If you choose this item and incorrect data is placed in the cell, a dialog box with title and text of your choosing opens. The only choices for this dialog box are to either Retry the input or Cancel out of the input, with Retry as the default button.

Warning Error

The Warning error choice gives users greater flexibility for input than what you might normally allow. It opens a dialog box with a title and text warning of your choosing. The choices for this dialog box are Yes, No, and Cancel, with No as the default button.

Information Error

The Information error choice also gives users greater flexibility for input than what you might normally allow. It also opens a dialog box with a title and text warning of your choosing. The choices for this dialog box are OK and Cancel, with OK as the default button.

  1. For now, make sure Stop is selected and press TAB to move to the next control. Focus moves to the Title edit box. As mentioned previously, if the title is redundant, leave it blank. This is, of course, an option some people may choose to use.
  2. Press TAB to move to the next control. Focus moves to the Error Message edit box, which is a multi-line edit box. This edit box has a maximum of 225 characters.

Creating a Date Input Field

Follow the same procedures above but create a Date Input field in a cell. Also, in this instance, use the Error Alert page to give the end-user a warning if the input is incorrect.

  1. Move to an empty cell in the worksheet and press ALT+D to open the Data menu.
  2. Press L to open the Data Validation dialog box.
  3. Press TAB to move to the Allow combo box for Validation Criteria. Choose "date" in the list and then press TAB to move to the Data combo box.
  4. For this example, assume the company was created in the year 2005, so choose "greater than" as the data validation and press TAB to move to the next control, which is the Start Date.
  5. Enter a date of 12/31/2004. This prevents anyone from typing a date prior to 01/01/2005.
  6. Press CTRL+TAB to move to the Input Message page.
  7. Press TAB to move to the "Show input message when cell is selected" check box and make sure it is checked.
  8. Move to the Input Message edit box and type something like the following: "Enter the date of your request."
  9. Press CTRL+TAB to move to the Error Alert page.
  10. Press TAB to move to the Show error alert after invalid data is entered checkbox, and make sure it is checked.
  11. Press TAB to move to the Style combo box and choose Stop.
  12. Press TAB to move to the Error Message edit box and type in something like the following: "You must enter a date greater than December 31, 2004."
  13. Press TAB to move to the OK button and activate it to close the Data Validation dialog box.

Excel 2007

For Excel 2007, the Data Validation dialog box is the same, and it is located in the Data tab of the ribbon:

  1. Press ALT followed by A to move to the Data tab of the ribbon.
  2. Press V for Data Validation within the Data Tools group.
  3. Press V again for Data Validation and the Data Validation multi-page dialog box opens.

Shading the Input Cells for Visual Effect

The cells where you have created input fields can be shaded to give a visually appealing look and to make them stand out from the rest of the text on the page. This makes it easier for people to find the fields if they are using a mouse and when entering information.

Excel Versions Prior to 2007

  1. Press ALT+O to go to the Format menu.
  2. Press E for Cells. The Format Cells dialog box opens. Alternatively, you can also press CTRL+1 to open the Format Cells dialog box.
  3. Press CTRL+TAB to move to the Pattern page.
  4. Press TAB to move to the list box that says "no color."
  5. Press DOWN ARROW to move into a list of the available colors and find one of your choosing.

NOTE: The color should be a light color, since darker background colors make text in the fields harder to read.


  1. Press ENTER to select the color of your choosing.
  2. Press TAB to move to and activate the OK button. The Format Cells dialog box closes and the background color is applied to the current cell or selected range of cells.
  3. Move to the next field and press CTRL+Y to repeat the process.

Excel 2007

  1. Move the cursor to one of the input fields you created earlier.
  2. Press ALT followed by H to move to the Home tab of the ribbon.
  3. Press H to move to the Font group and select Fill colors. A Theme colors gallery appears. Alternatively, you can also press CTRL+1 to open the Format Cells dialog box, but you need to press CTRL+TAB to move to the Fill page and then TAB to move to the "no color" button, followed by DOWN ARROW to move into the colors list.
  4. Press the ARROW Keys to choose a color for the background shading of that cell.
  5. Press ENTER when finished to close the Theme colors dialog box.
  6. Move to the next cell where an input field is and repeat the process, or press CTRL+Y to repeat the process without having to go to the ribbon.

Inserting Hyperlinks that Point to Input Fields

To make navigation easier for JAWS or MAGic users, insert hyperlinks into the worksheet that point to each of the major fields you want users to move to. You can put the hyperlinks anywhere in the worksheet, but if you want them to be invisible to sighted users, read the next section.

Excel Versions Prior to 2007

  1. Put the cursor in the cell where you want the first hyperlink to be. This is not the cell you want the hyperlink to point to, but a cell that will contain the hyperlink. Usually this is near the top of the form, but it could be anywhere in the worksheet and still be easy for JAWS or MAGic users to find.
  2. Press ALT+I to open the Insert menu, followed by the letter I for Hyperlink. Alternatively, press CTRL+K to open the Insert Hyperlink dialog box. Focus lands in the Address edit combo box.
  3. Press TAB until you reach the button "Existing File or Web Page" and change it to "Place in this Document" by using the DOWN ARROW key. Focus moves to the "Type the Cell Reference" edit box.
  4. Type in the cell reference for the first field on your form. For example, it might be A10.
  5. Press TAB to move to the "Text to Display" edit box and type the text you want the JAWS or MAGic user to hear in the list of links. For example, you might type "Customer Name" for the customer's name field.
  6. Press TAB to move to the ScreenTip button and activate it with the SPACEBAR. Enter the same or similar text here to have a descriptive pop-up message appear visually when a mouse hovers over a link. This is optional, since you may be hiding the links anyway, but the author still prefers to add this.
  7. Press ENTER to close the Set Hyperlink ScreenTip dialog box when finished. Focus returns to the Edit Hyperlink dialog box.
  8. When you are finished with the above, press TAB to move to the OK button and activate it with the SPACEBAR. The new hyperlink is now inserted into the document.

Excel 2007

  1. Press ALT followed by N to move to the Insert tab of the ribbon.
  2. Press I to choose Hyperlink. Focus lands in the Address edit combo box.
  3. Alternatively, you can still press CTRL+K to open the Insert Hyperlink dialog box.

Follow the instructions listed above for prior versions of Excel to continue inserting a hyperlink into the worksheet.

Making the Hyperlinks Invisible to Sighted Users

Hyperlinks can clutter up the appearance of a form, but not if you make them invisible! After you have created all of the hyperlinks you need, move to them and select them all if they are contiguous to each other. If not, select them one at a time and perform the following steps:

Excel Versions Prior to 2007

  1. Press ALT+O to activate the Format menu.
  2. Choose Cells in the menu. Alternatively, you can also press CTRL+1. The Format Cells dialog box opens.
  3. Press CTRL+TAB to move to the Font page.
  4. Press ALT+C to move to the Color combo box. Use the ARROW Keys to find the color white and ENTER to select it.
  5. Press TAB to move to the OK button and activate it. The Format Cells dialog box closes and the link is now invisible to the sighted user and will not show up in the print copy either. However, JAWS and MAGic users can find them using the list of links, INSERT+F7.

Excel 2007

  1. Press ALT followed by H to move to the Home tab.
  2. Press FC for font color and a Theme Colors gallery appears.
  3. Press DOWN ARROW to move into the list and use the ARROW Keys to find white.
  4. Press ENTER to close the Theme Colors gallery. The link now becomes the same color as the background color.

Setting the Print Area

Setting the print area helps reduce wasted paper and ensures that the form is the proper number of pages.

Excel Versions Prior to 2007

  1. First, press CTRL+HOME to move to the first cell, A1.
  2. Then press CTRL+SHIFT+END to select from cell A1 down to the bottom right side of any information you entered into the worksheet.
  3. Press ALT+F to open the File menu.
  4. Press T to choose Print Area in the menu.
  5. Press ENTER to choose Set Print Area. The dialog box closes and the print area has been set.

Excel 2007

  1. Press ALT followed by P to move to the Page Layout tab of the ribbon.
  2. Press R to choose Print Area.
  3. Press S to Set Print Area.

NOTE: To prevent empty "blank" sheets from printing, try to keep all of your hidden hyperlinks within the page print area, or alternatively, exclude them from the actual printed document by deliberately placing them outside the page print area. Either way is fine for JAWS or MAGic users.


Help Text in the First Cell, A1, Using an Input Message

Put the initial instructions on how to navigate the form into cell A1 using an input message. Be sure to move the focus to this cell prior to saving the worksheet for the final time. That way, the help text will be seen by sighted users as soon as the worksheet opens, and JAWS or MAGic reads the instructions out loud to their users.

NOTE: Here is some recommended help text:

Use the list of links or press TAB to move to input areas. Press UP or DOWN ARROW in column A to read through the document.


Setting the Input Fields to the Unlocked State

Before protecting a worksheet, you need to unlock the fields where you want people to fill in data; otherwise they will not be able to fill in the form. Also, this protects the text and other elements elsewhere in the form from being changed.

Excel Versions Prior to 2007

  1. Move to the first cell you want to unlock.
  2. Press ALT+O to open the Format menu, followed by E to open the Format Cells dialog box. Alternatively, press CTRL+1.
  3. Press CTRL+TAB to move to the Protection tab page.
  4. Press TAB to move to the checkbox "locked" and press SPACEBAR to uncheck it.
  5. Press ENTER to close the Format Cells dialog box.
  6. Repeat the process for all cells where data is to be entered.

Excel 2007

  1. Press ALT followed by H to move to the Home tab of the ribbon.
  2. Press FN to open the Format Cells dialog box. Alternatively, press CTRL+1.
  3. Press CTRL+TAB to move to the Protection tab page.
  4. Press TAB to move to the checkbox "locked" and press SPACEBAR to uncheck it.
  5. Press ENTER to close the Format Cells dialog box.
  6. Repeat the process for all cells where data is to be entered.

Protecting the Worksheet

Protecting the worksheet is the final step prior to testing the form and finishing the project.

Excel Versions Prior to 2007

  1. Press ALT+T to open the Tools menu.
  2. Press P to move to and open the Protection submenu.
  3. Press ENTER on Protect Worksheet (or Protect Workbook if desired). A Protect Sheet dialog box appears with focus in the password field.
  4. Type in a password if you want to prevent others from changing the form.
  5. Press ENTER and you are prompted to type the password again.
  6. When finished, press ENTER a final time and the Protect Sheet dialog box closes.

Excel 2007

  1. Press ALT followed by R to go to the Review tab of the ribbon.
  2. Press PS to begin protecting the worksheet. A Protect Sheet dialog box appears with focus in the password field.
  3. Type in a password if you want to prevent others from changing the form.
  4. Press ENTER and you are prompted to type the password again.
  5. When finished, press ENTER a final time and the Protect Sheet dialog box closes.

Sample Excel Forms Provided

Please take a look at the form examples for Excel created by the author as one example of an accessible form that uses the techniques discussed in this paper. There are two versions, one for Excel 97-2003 and one for Excel 2007.

EXERCISE: As you move through and explore this form, take notice of some of the different elements within it as follows:


  1. Hyperlinks reside in cells D1 through D6
  2. Some cells are merged to provide a more uniform look. Using Office 2007, after selecting two or more cells to merge, press ALT followed by H, then M, and then choose to either merge and center or merge cells only.
  3. Cell A21 contains a title using the Name feature in Excel that reads to JAWS and or MAGic users the column headers such as Quantity, Unit of Measure, Description, and Cost per Unit when navigating from left to right in those particular columns. To learn more about how to use the Name feature to create titles in Excel please read the DAISY book entitled Column and Row Titles in Excel (DAISY 15.5 MB) found on the JAWS Training Web site.
  4. The Quantity column has a validation type of "any value" and has no error alert.
  5. The Unit of Measure column has a validation type of "list" and uses the information error alert, which allows the end-user to either choose something from the list or to type in something that does not exist in the list.
  6. The Description column has a validation type of "any value" and has no error alert.
  7. The Cost per Unit column has a validation type of "decimal" and has a stop error alert. In this particular case the end-user is limited to a value between zero dollars and $1000. The currency format in Excel was also applied to the items in this column.
  8. Cell D27 contains a formula that calculates the total cost for the items requested. The formula multiplies the quantity times the cost per unit for all items entered and then sums them all.
  9. The Date Needed By field has a validation type of "date" and in this particular instance the author assumes the company began in the year 2005, so there is a stop alert applied that requires date input greater than 12/31/2004.

Contact Information

Freedom Scientific Training Department
training at FreedomScientific.com
800-444-4443 ext 1016

Copyright

Published by Freedom Scientific, Inc.
11800 31st Court North
St. Petersburg, Florida 33716-1805, USA
http://www.FreedomScientific.com
Phone toll free 800-444-4443
Phone outside of the US and Canada 727-803-8000
FAX 727-471-7927

Information in this document is subject to change without notice. This learning module is protected under copyright by Freedom Scientific. Please feel free, however, to use this module to create your own training materials to help others learn JAWS or MAGic. Also feel free to share this training module with your friends and colleagues who might be interested in learning more about JAWS or MAGic. We ask only that you acknowledge Freedom Scientific as the copyright holder of any of this material you use.

JAWS® and MAGic® are registered trademarks of Freedom Scientific, Inc., St. Petersburg, Florida and/or other countries.

Microsoft® and Excel® are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

All other trademarks are the property of their respective owners.