Title:Creating Boiler Plate Documents with VBA
Issue: Spectrum Mar/Apr '99
author:Nathan Rector
company:Natec Systems
email:nater@northcoast.com
http:www.northcoast.com/~nater/
Microsoft Excel and Word are the most commonly used programs supplied with Microsoft Office. Depending on the business that they are used in dictates to what extent they are used. There are times when you need to create more control in these programs to make them more functional or to restrict specific items from the users access.
VBA gives the developer this ability. If you are working in the legal field, there are common legal documents that are used over and over again. These documents just require the blanks to be filled in. The same can be found in the medical, banking, and real estate fields.
Since these documents are boiler plates, you don't want the user to alter them except in specific areas. A lot of the information is likely to come from your database in addition to what the user types in.
Word has several functions that allow you to create boiler plate documents. With a combination of these functions and VBA, a developer can restrict the alteration of these documents as well as add database information to the document automatically.
The first step is to create the document in Word. Once the document has been created, replace the area you wish to give the user access to with Form Fields. You can find the Tool bar that has the Form Field select on it by going to View|ToolBars|Forms. This will cause the Forms Toolbar to show.
There are 3 different types of input controls a developer can add to a document: Text box, Check Box, and Combo Box. Place a Text Box anywhere you want to have the user input free hand text; for example, comments or specific instructions. Place a Combo Box anywhere you want to user to select from pre-selected list of information; for example, a specific illness or type of bank account. Place the Check box where you want to user to select a specific paragraph or a yes/no selection in the document.
The Combo box allows a developer to populate the drop down list with information from either VBA or from the properties screen. More than likely, the information in this dropdown list will come from the database. A little later in the article I will get to the code used to populate these fields.
When you are done creating the document and you have placed the Form Fields on the document, you can do a quick test to make sure this is what you want to do by clicking on the button on the Form Fields toolbar that locks the document. When you do this, it will allow you to tab between the Text Boxes, Check Boxes and Combo Boxes as if you were the user.
When you are satisfied with how the document works, then it’s time to place the VBA code into the document that will allow you to pull information into the document as well as control access to specific functions that the user does not need access to.
Each Document in word has 3 Events you can place code in: Open, Close, and New. We will be using the Open and Close Events in this example.
The first thing you need to do is populate the combo boxes if there are any. You can do this with VBA with the following code, where DropDown1 is the name of the Form Field:
FormFields("DropDown1").DropDown.ListEntries.Add Name:="Natec Systems"
If you want to set the value to use in each field using VBA, then the syntax is, where DropDown1 is the name of the Form Field:
FormFields("DropDown1").Result = "Natec Systems"
FromFields("Text1").Result = "Nathan Rector"
Now that I’ve covered how to add and modify the values in the Form Fields, add the needed code to keep the user from changing or modifying your boiler plate document. To keep the user from saving the information in this document to another name and modifying it’s content, we will disable the Save options. To keep the user from modifying or seeing the VBA code, we will disable the Macro function. And lately, to make sure user can only input information into the Form Fields, we enable to "Protect Form" option. Please be careful modifying the following code, as long as this code is running it will cause these options to no longer function until Word is shut down completely. If the user is not careful, these alterations can be saved permanently.
Private Sub DisableMenu(bVisible As Boolean)
On Error Resume Next
' removes Menu Options
CommandBars("File").Controls("Save").Visible = bVisible
CommandBars("File").Controls("Save As...").Visible = bVisible
CommandBars("File").Controls("Save as HTML...").Visible = bVisible
CommandBars("File").Controls("Versions...").Visible = bVisible
CommandBars("Tools").Controls("Protect Document").Visible = bVisible
CommandBars("Tools").Controls("Unprotect Document").Visible = bVisible
CommandBars("Tools").Controls("Macro").Visible = bVisible
CommandBars("Standard").Controls(3).Enabled = bVisible
CommandBars("Forms").Controls(9).Enabled = bVisible
End Sub
Now combine all this into the Open Event of the document:
Private Sub Document_Open()
On Error Resume Next
' locks the document
ActiveDocument.Protect Password:="", NoReset:=False, Type:=wdAllowOnlyFormFields
' Adds dropdown list options
FormFields("Name").DropDown.ListEntries.Clear
FormFields("Name").DropDown.ListEntries.Add Name:="Nathan"
FormFields("Name").DropDown.ListEntries.Add Name:="Tracey"
FormFields("Name").DropDown.ListEntries.Add Name:="Natec Systems"
' removes Menu Options
DisableMenu False
End Sub
Since you have disabled some of the menu options, you must reenable them in the Close Event to keep the changes from being saved permanently by accident:
Private Sub Document_Close()
DisableMenu True
End Sub
With a little alteration to the VBA code in the Open Event, you can pull data from your MultiValue Database and populate the Dropdown or Text Fields with information from the database.