Daily Dose of Excel

Using a userform to enter data into a worksheet is a common need. Excel provides a data form under Data > Form that does this. It is, however, limited to what you can do and not at all customizable. John Walkenback has an excellent Enhanced Data Form that will satisfy about 99% of Excel users. This series of posts is for the other 1% that need even more customization (or those of us that are just sadistic and like to build stuff ourselves).

What I hope to describe here is not a general purpose data form, but one built specifically for a particular database. This will also not be a ready made solution, unless your fact situation happens to exactly fit the example. What it will be is a demonstration of the techniques that you can use to build your own form should the need arise.

One more disclaimer: This example form is not necessarily how I would build a form for my own use. I, like you, might want more, less, or just different features than what are described here. I tried to show a variety of features that people may want and the techniques that I would use to implement them. You know, for a guy with no legal department, I sure have done a lot of typing without really saying much. So let’s get on with it,

Setting Up the Form

This example will have a worksheet that contains contact information and a form to enter and edit that information. The spreadsheet looks like this:

and the userform looks like this

The userform, UContact, has these fields and the fields have these Tag properties.

Name(Tag)
tbxFirst(0)
tbxLast(1)
tbxAddress(2)
tbxCity(3)
cbxState(4)
tbxZip(5)
scbContact()
cmdClose()
cmdSave()
lblFirst()
lblLast()
lblAddress()
lblCity()
lblState()
lblZip()

The Tag properties denote the offset from column A where the data resides in the worksheet. tbxFirst has a Tag of zero because it’s in column A. The empty parentheses indicate no Tag set for that field.