|
Recently, one of the members of my Word VBA group told me that although she was new to creating automated forms in Word, she wanted to create a form that would help preschoolers learn their multiplication tables.
Once I got over the shock that preschoolers would be working on multiplication tables (when I was that little, I was lucky I knew my ABCs), I decided this would be a great article idea. (Thanks for the question, Nicola!) I got busy creating a prototype. In this article, I explain the details of my solution. Note, this is not a beginner's article, and if you're new to creating forms or writing VBA, you can get more training by reviewing the articles linked on my TechPage..
When you contemplate an automated form project, first consider the users. If they are seniors, you don't want to use small fonts since they make the form more difficult to read. If the users are new to computers, you should give them lots of tips. Of course, creating a form for preschoolers has its own considerations, and I explain more about that later in the article.
Creating the Form
To create a form, first you need to open a new Word document and immediately save it as a template. Forms should be saved as templates. (If you need more information on templates, please refer to this article: Normal.dot Template-Explained)
Choose View|Toolbars|Forms to make sure the Forms toolbar is enabled. With the Forms toolbar turned on, you then insert three Textbox form fields. You are inserting one for each of the numbers to be multiplied and a third for the answer. After that, type the word Answer and insert a single bookmark. Finally, you insert a command button from View|Toolbars|Control Toolbox. The image below shows the form prototype.
The user types a number in the first box, tabs to the next textbox to enter a second number, and then tabs to the last box to enter the answer. Then they click the command button to see if their answer is correct. Text is displayed to provide feedback. Because the users are small children, you don't want to simply say "Right" or "Wrong," but include some excitement when they answer correctly and offer encouragement when they're wrong. So this form displays either "Yes, you are correct!" or "Sorry, that is not correct. Try again."
If you prefer, you could have the form automatically calculate the answer or have the calculation link on a menu, rather than using a command button. But kids just love to click things, so I used a big button to give them an opportunity to interact.
The final step in creating the form interface itself is to lock it as a form. To lock it, you can simply click the Lock icon on the Forms toolbar, since this form doesn't use a password. If you did decide to include a password, you need to choose Tools|Protect Document to add a password. In this case, I decided to leave the password decision up to the teacher, since this form is just a prototype to get her rolling.
Writing the Code
The first thing you always want to do when you create a form is change the default bookmarks supplied to the form fields. Using the default Text1, Text2, Text3 can be confusing (especially if you have several boxes in a form). Don't make it harder on yourself. To rename the fields, unlock the form, double click each of the Textboxes and change the Bookmark name to something easier to identify. For example, I called them: bkOne, bkTwo and bkStudentAnswer.
Note that, even though the users enter numbers, the Data Type is set to Regular text. (I explain why shortly.)
Now press Alt + F11 to enter the Visual Basic Editor. You write your code in a new code module. To insert a code module into the template project, choose Insert|Module. (Note that in the example, I also renamed the code module to modMain out of habit, since I often have several modules in a project.)
Just as the form could have been prettier, there also are more sophisticated ways to write the code. But because this form is a prototype for a form newbie who doesn't know VBA, I opted to use as elementary a process as possible to make it easier for her to learn.
First, you need to write a procedure called Calc, which runs when the user clicks the button. This procedure contains the main calculation code. In it, you declare variables to contain the entered content and conversions that will be needed.
'declare variables
Dim strOne As String
Dim intOne As Integer
Dim strTwo As String
Dim intTwo As Integer
Dim strStudent As String
Dim intStudent As Integer
Dim intAnswer As Integer
Dim strSolution As String
Now you set the variable equal to each of the textboxes to capture the content that is entered by the user. Next, there is an If…Then statement to validate that the user has entered a number (see below). Remember that the Data Type was left as regular text. If I had set it as a number and the preschooler accidentally entered an alphabetic character, it would automatically change to zero, which would be confusing to the user. Plus the teacher might not realize what the student did to cause the zero to appear!
By setting the data type as text, you can check to see if the content entered is a number. If not, you can display a more user-friendly error message that tells the user to enter a number and the alpha character is still displayed. This error is a lot easier to decipher than a letter that magically changes to zero! At this point, the code "bails" out of the subprocedure to stop the action, so the user can correct the mistake and avoid any additional, needless errors.
By validating the entered content as a number, you also are verifying that something has been entered. So you don't need to worry about adding more code to also check for empty variables.
'set values to input
strOne = ActiveDocument.FormFields("bkOne").Result
'validate number
If Not IsNumeric(strOne) Then
errMessage
'bail
Exit Sub
End If
End If
'set values to input
strTwo = ActiveDocument.FormFields("bkTwo").Result
'validate number
If Not IsNumeric(strTwo) Then
errMessage
'bail
Exit Sub
End If
End If
'set values to input
strStudent = ActiveDocument.FormFields("bkStudentAnswer").Result
'validate a number
If Not IsNumeric(strStudent) Then
errMessage
'bail
Exit Sub
End If
End If
Because the Textboxes are returning a String, the variables need to be converted into numbers to be calculated, so you use the CInt() Function to convert each variable to an Integer.
'convert strings (text) to numbers (integers)
intOne = CInt(strOne)
intTwo = CInt(strTwo)
intStudent = CInt(strStudent)
Now I calculate the two factors to capture the correct answer into the intAnswer variable.
'multiply given values
intAnswer = intOne * intTwo
The correct answer is then compared to the number the student entered as his or her answer. At this point, the strSolution variable also determines which text will be displayed. If the student's answer is equal to the correct answer, they get a "Yes." If it is not equal to the correct answer, they're encouraged to give it another try.
'decipher if answer is correct and set answer text value
If intAnswer = intStudent Then
strSolution = "Yes, you are correct!"
ElseIf intAnswer <> intStudent Then
strSolution = "Sorry, that is not right. Try again."
End If
Finally, within this procedure, the answer text needs to be inserted into the document. You could insert another textbox and set the value to the answer, but then the user could retype over the answer. Or you also could set the textbox to Not Enabled, so they could not type into it. Another way is to just insert a single bookmark and slip the text into the document. However, using this method means you need another procedure to rewrap the inserted text with the same bookmark so that the answer text can continually be replaced each time the user clicks the button. So the next line of code simply calls the rewrap procedure, along with the needed variables, which are the name of the bookmark ("bookAnswer") and the content to be inserted (strSolution).
'put solution text in document by calling
'rewrap sub, with added bookmark and content
RewrapBookmark "bookAnswer", strSolution
The calculation code itself is complete, but you still need to do a few things to finalize this process. Remember that you have called an error message, so you need to create that subprocedure.
Sub errMessage()
MsgBox "You must first enter numbers in the boxes!"
End Sub
You also need the RewrapBookmark subprocedure that you call to insert the Answer variable into the document. Remember the call was written as:
RewrapBookmark "bookAnswer", strSolution
Whenever I need to have content rewrapped within a single bookmark, I use the code below. Note that the subprocedure requires a bookmark name and a text string to be passed. Those two items are required when you call this procedure. In this case, the bookmark to be updated is "bookAnswer" and the text string is whatever ends up being contained within the strSolution variable (as shown in the line of code above).
Sub RewrapBookmark(ByVal vBookmarkToUpdate As String, vTextToUse As String)
'when accessing a bookmark, which is not accessible
'when the form is locked, the lock must be toggled
ToggleFormLock
'set bookmark range w/var passed
Dim bkRange As Range
Set bkRange = ActiveDocument.Bookmarks(vBookmarkToUpdate).Range
'vBookmarkToUpdate is the name of the bookmark to use
'vTextToUse is the data dump variable
bkRange.Text = vTextToUse
ActiveDocument.Bookmarks.Add vBookmarkToUpdate, bkRange
'toggle relock
ToggleFormLock
End Sub
The code also calls a subprocedure called ToggleFormLock. This subprocedure needs to run to toggle the document protection. Remember that a form must be locked to activate the form fields. That's not a problem when capturing information, but you can't insert information into a locked form. So this procedure is called to open the form before you insert the string variable. It's then called afterwards to relock the form.
Of course, this toggle subprocedure also needs to be added into the code module so it is found when called. The password portion of the code is commented out in the example because I'm not using a password in the code. But I always leave it there, so if you decide to add a password later, it's easy to add it back in. (The capital STR_ is my naming convention to designate that the name is a constant; it is set within the General Declarations at the top of the module.)
Sub ToggleFormLock()
'if locked, unlock, else lock
If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect ', Password:=STR_DotPassword
Else
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True ', Password:=STR_DotPassword
End If
End Sub
You're almost done, but you still need to add the code to the command button so it runs when the button is clicked. To add code to an ActiveX control that has been added to a document from the Control Toolbox, you need to be in Design Mode, which can be toggled on/off from the Control Toolbox.
Once you're in Design Mode, you can right-click the command button and click View Code to jump you into the controls event. In this case, it's the Click event because it is a button. Any ActiveX controls placed in a document have code that goes into the ThisDocument code module. If I were following my own advice, I should have changed the Name property of the button to a more recognizable name. But because I only had one button, I got lazy and left the default name. In the code below, you enter the call to the Calc procedure.
Private Sub CommandButton1_Click()
'calls code to run calc
modMain.Calc
End Sub
There's just one final enhancement. The strSolution (the text displayed to tell them whether the answer is correct or not) is updated each time the user clicks the Check Answer button. But when little children enter a new answer, they may not realize that the currently displayed text is describing the last round and doesn't yet indicate whether their new answer is correct. To help clarify things, the Clear procedure erases the last right or wrong text as soon as the user clicks within the third textbox to type a new answer. This way, when the child is ready to pay attention to whether their new answer is correct, they won't see any text yet displayed. This feature helps remind them they need to click the button to get a decision on the newly entered answer.
Sub ClearPreviousAnswer()
'update solution in document
RewrapBookmark "bookAnswer", ""
End Sub
This code (above) calls the RewrapBookmark subprocedure again, but this time the same bookmark is set to nothing (since nothing is between the quotes in the string location). It causes the last text to disappear, but leaves the bookmark for the next click update.
The last step for this enhancement is to add it into the Entry location of the strStudentAnswer textbox. Double-click that form field and match the subprocedure into the Entry box as shown below.
Now, when the child clicks inside that box to type a new answer, this code is called and the text that is inserted into the Answer bookmark will be nothing. The text from the last answer disappears.
If you add an icon for this template on the computer's desktop, users can just double click to start a new document form. They can enter any numbers they want into the first two boxes and enter their solution into the third box. Then they click the button to see if the solution they entered is correct.
All the modMain code described in this article is available in a text file that you can download here.
If you decide to create your own version of this form, be sure to test it and test it again! If you have problems getting it to work correctly, please feel free to stop by the water cooler in my Word VBA group where hundreds of forms developers hang out helping each other learn this stuff!
|
Department
