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,

  • It can clarify the nature of what should be inputted.
  • Finding things on a form is often easier than finding things on a spreadsheet.
  • Easier to validate what is entered.

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.

  • The location of the tournament.
  • The date of the tournament.
  • The tournament director.
  • The amount of money you were paid.

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.

Private Sub cmdExit_Click()
Unload frmTournament
End Sub

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
  • Add new entries
  • Rollback if they've made an error or no longer need the entry
  • Post if they have entered the info and it needs to pass more input validation and actually update the data source

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

Private Sub cmdAddNew_Click()
txtLocation = ""
txtDate = ""
txtDirector = ""
txtPay = ""

End Sub

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.

Private Sub cmdDelete_Click()
txtLocation = ""
txtDate = ""
txtDirector = ""
txtPay = ""

End Sub

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.

Private Sub cmdUpdate_Click()
Dim NextRow As Integer
' Make sure the sheet that will contain the data is active

' Determine the next empty row
NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer the information
Cells(NextRow, 1) = txtLocation.Text
Cells(NextRow, 2) = txtDate.Text
Cells(NextRow, 3) = txtDirector.Text
Cells(NextRow, 4) = txtPay.Text

' Clear the textboxes for the next entry
txtLocation = ""
txtDate = ""
txtDirector = ""
txtPay = ""

End Sub

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.

Private Sub cmdUpdate_Click()
Dim NextRow As Integer
Dim Msg As String
Msg = ""
' Making sure a number is inputted for the pay
If IsNumeric(txtPay.Text) = False Then

Msg = "You did not enter a number for the amount of money you were paid." &  vbCrLf

End If
' Making sure a date is inputted for the date
If IsDate(txtDate.Text) = False Then

Msg = Msg + "You did not enter a viable date in the date for the tournament"

End If
' Giving the user some feedback about there detectable errors
If Msg <> "" Then

MsgBox Msg
Exit Sub

End If

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.