Developing a UserForm to Write to a Spreadsheet
Some Background. It is often the case that you want to develop a form to help a user put in entries to a spreadsheet. This can be advantageous for many reasons. For example,
In many cases it might be better to use a database for this sort of situation, but in general, people are much less familiar with databases. In the past I worked on a program that would help companies, carrying large inventories with a large variety of products, to forecast their demand to help them reduce inventory costs. I developed the initial program in a database environment figuring that was where most firms would have such information. As we started truly interacting with a variety of firms, such as a regional automobile parts warehouse, a chemical distributor/manufacturer, a pipeline/pump/valve distributor/manufacturer, I discovered they were all using spreadsheets to maintain their information on their monthly inventory levels. The number of products they were tracking was in the tens of thousands for the automobile parts warehouse, in the thousands for the pipeline/pump/valve distribution and in the hundreds for the chemical distribution. Think of working with spreadsheets on this scale! One of the main upfront issues associated with using spreadsheets in such a situation has to do with input, access and retrieval of data. This will be the focus of our next few webpages. Since this is just a first course in such issues I am not going to start working at this level of sophistication, but I definitely intend on moving in the realms of functional reality. Let's assume you are a referee for volleyball tournaments and you want to keep records of all of the tournaments you've been refereeing. You want to create a form that helps you keep track of the following sorts of things.
While there are plenty of other things you are likely to want, or related things you might have on a different spreadsheet within the same workbook, we will start with this. Developing the UserForm. You are going to want to start a new workbook and create a userform that looks like the following. |
| The following table lists the controls and the properties associated with each. |
| Control | Property | Value |
| Form | Name | frmTournament |
| Caption | Input Tournament Info | |
| Font | Tahoma - 12 PT - Bold | |
| Label | Name | lblLocation |
| Caption | Location | |
| TextAlign | Right | |
| TextBox | Name | txtLocation |
| Label | Name | lblDate |
| Caption | Date | |
| TextAlign | Right | |
| TextBox | Name | txtDate |
| Label | Name | lblDirector |
| Caption | Tournament Director | |
| TextAlign | Right | |
| TextBox | Name | txtDirector |
| Label | Name | lblPay |
| Caption | Pay | |
| TextAlign | Right | |
| TextBox | Name | txtPay |
| CommandButton | Name | cmdAddNew |
| Caption | Add New | |
| CommandButton | Name | cmdDelete |
| Caption | Delete | |
| CommandButton | Name | cmdUpdate |
| Caption | Update | |
| CommandButton | Name | cmdExit |
| Caption | Exit |
The code for the cmdExit button is the easiest and we
have seen it before. It is just the following.
Now we will move onto the other command buttons in the next section. |
Add New - Delete - Update.
There is a widely used approach for developing forms that work with
storing data that is based on the three basic operations called Add
- Rollback - Post. This approach is based on the belief
that a user will need to
Since these words are not as likely to communicate that well as command button captions we will use Add New - Delete - Update as captions and the way to refer the approach we will use for one of our fundamental sets of command buttons. As we have seen it is important to do quite a bit of input validation before even trying to perform calculations. This is also the case when storing and accessing data. This allows a user the ability to input data and make corrections before they have actually entered the data into the data source. It is also often necessary to delete data that is either currently being inputted or delete useless or inaccurate data currently in the data source. We will now start developing the event code associated with each of these command buttons. At this stage we are going to set up the form so that it can add new entries at the bottom of the existing tournament information in the spreadsheet. This implies that the first pass will not be terribly sophisticated for the Add New or Delete buttons, though the Update button will require some serious effort. The code for the Add New button just blanks out the current entries since the user is supposed to press the Update button first. When we enhance this event code in the next section we will ask the user whether they want the program to automatically update before starting a new entry. The code follows
The event code for the Delete button will essentially be the same. It will be enhanced in the next webpage when we make it possible for the user to also move through the currently held entries rather than just add new entries to those that are already listed.
Now we need to set up the Update command button code so that it will take the entries currently in the form and appropriately append them to the bottom of the data source.
Input Validation. We return to our usual code motivator, input validation. We should probably check to make sure that txtDate contains a date and txtPay contains a number. It is very difficult to validate the inputs for a location or tournament director since these could be just about anything. Before we even determine what the next available row is for writing in information we should at least guarantee that we have dates for dates and numbers for pay. Now the beginning section of code will be like the following.
This will do what we want. We will make this more elaborate so that the older information can be viewed through the form in the next webpage. |