Validating user input in excel vba
Soliciting data from users is a common use for a Word User Form, but a typo, a correctly spelled but invalid value, or even a control left blank can render your document unusable.Fortunately, you can include a bit of VBA code to check input values for specific attributes.You can think of validating data in terms of opening a door, and that door can be: Names present the most common use of an open control; the door is open and any name can pass through to your document.Displaying the input value and asking the user to confirm the value before committing it can help, but beyond that, you can do little to validate this type of input—anything the user enters can pass through the door. The value must pass a few tests before the code allows it to pass through.A closed control forces the user to choose an item from a list.In this way, you protect your data from typos and invalid entries because the user never enters a new or unique value from the keyboard.Only when the user selects a value from the available options does Word open the door and let the value in.For instance, you might check US ZIP code entries by ensuring that each character is a numeric digit and that there are five (or nine) characters.
In this article, we'll look at simple examples of validating input values before committing them to the document.
This article's example form and controls assume you have basic knowledge of Word's User Form object.
If you don't know how to create a User Form, consider reading 10 steps to creating a Word userform for addressing letters before you continue.
To simplify the examples, we'll change only the properties necessary to support the technique at hand.
You can work with your own User Form or download the demonstration or file.Note: Don't copy and paste the code from this web page, because some web-based characters will return errors.