MS Excel Chapter 6: Forms

Instructions

Section 6.1: Form Title and Placement

All forms should have a clear and meaningful title in Cell A1.

This form does not have a title. Students who use screen readers may struggle knowing what the content in the form is about. They may not know the worksheet is a form at all. Always include a title to give them a prompt or queue to know what they are supposed to see.

Open

This form has a title. Now the student will have an easier time figuring out what the form is about.

Open

Another thing to keep in mind is that all forms should be placed on separate spreadsheets.

Section 6.2: Form Design

When designing a form, you need to keep the following rules in mind.

Rule 1: Use Simple Designs for Forms that are not Overly Complicated

Rule 2: You Should Include Instructions on How to Navigate and Complete the Form

Rule 3: Every Form Input Field must have a Label or Instructions Visible on the Screen. Input Cells Must Also be Next to the Cell Containing the Question or Description

Rule 4: Input Cells should be Shaded to make them more Visually Discernible using a Color other than Yellow

Rule 5: Do Not Leave Any Cells Blank

Rule 6: Consider Adding Contact Information to the Form

Section 6.3: Data Validation

Think of Data Validation as a way to assist people to filling out the form correctly. They act like post it notes or hints with little instructions. When using Data Validation, be sure to give a clear message and provide all relevant information.

To use Data Validation,

1. Create your form and click in one of the fill in boxes.

2. Click the Data Tab, then choose Data Validation.

3. In the Data Validation Box, choose Input Message. Then type in some instructions that will help the reader with the form. Click OK. Your end result should show a yellow square with your instructions inside it.

Section 6.4: How to Provide Clear Error Alerts

Error Alerts are a great way to give people some guidance on whether they have filled out the form properly or not. To create an error alert,

1. Choose a category you want an alert to appear for.

2. Go to the Data Tab, then click the Data Validation Button.

3. In the Data Validation Settings Tab, change them to suit your needs. In Error Alert, type in a clear message on what has to be changed in the file in order to continue with the page.

4. If the wrong information is typed into the box, your warning should appear.

5. Finally, make sure that if you have fields that are required, you need to add an alert to the reader so they know how many input fields they need to fill.

Section 6.5: Form Controls and ActiveX Controls

While form controls like buttons, and check boxes are really cool to use, the are not ideal for accessibility. Most controls require users who can only use keyboard to go through a huge number of steps. Sometimes it can take a very long time for a person to check even one box. This must be changed. Instead of making check boxes and buttons, try creating a more accessible way for information to be submitted. One example is a dropdown menu. Another is to have the user type the information after being given a few choices.

One exception to the rule on using buttons is that you are allowed to make simple ones that run a macro. A good example is a Clear Form button. The Clear Form button will clear your form and is accessible for users using screen readers or only keyboards. To create your Clear Form button,

1. Go to the Developer Tab.

2. Click Insert, and in the Form Controls section, click the Form Control button.

3. Click and drag in cell B1 to create a button at a size you want.

4. The Assign Macro box will appear, click Cancel.

5. Now double click your button and type “Clear Form.”

Now that your button is made, we need to assign a macro and use some VBA code to make the button perform an action. To do this,

1. Go to the Developer Tab and click Visual Basic.

2. In the Visual Basic box, click Insert and choose Module.

3. In the text box, type in the code for your button.

   1. In the code, it says “Are you sure you want to clear the form?” This is in the code because a person can read “Clear Form” and know what it is. But a screen reader will not be able to read the Clear Form button. Just that a button is present. This question will be read by the screen reader and will alert the reader.

   2. Be sure you made changes to the code if you need it. For example, changing the range to reach your whole form.

4. When you click the button, the question will pop up. If you answer “Yes”, the form will be cleared. If you answer “No,” nothing will happen.

5. Right click your button and click Assign Macro in the expansion menu.

6. In the pop-up box, click the Clear Form Macro and click OK.

7. Go back to the Developers Tab and click on the Macros button.

8. In the pop-up box, click the Options button and change the short cut to Control + Shift + C.

   1. Clicking those buttons in that order will allow students who only use keyboards to use the macros and clear the sheet.

9. To fulfill accessibility requirements, add instructions to cell A1 on how to use the Clear Form button.

Section 6.6: Locking and Protecting

Forms should always be locked and protected. This is done to protect users from changing information by mistake. To lock and protect your form,

1. Highlight the input cells, right click, and select Format Cells.

2. In the dialog box, click the Protection Tab and un-check the Locked Option. Click OK.

3. Go to the Review Tab of the ribbon. Select Protect Sheet.

4. In the Protection Sheet Dialog Box, make sure “Select Locked Cells” and “Select Unlocked Cells” are checked. Everything else can be unchecked. Click OK.

5. Optional: You can create a password.

6. Now with your spreadsheet saved, a user will only be able to make changes to the input fields. If they tried to make changes, an alert message will appear.

Related articles