Linking Userforms and Worksheets Part I

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 Walkenbach 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:

LUWP1P01

and the userform looks like this

LUWP1P02

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.

2 thoughts on “Linking Userforms and Worksheets Part I

  1. Sure wish you would have taken this to completion where you actually show the VBA code for transferring the information from the different boxes on the UserForm
    to the table of data on your worksheet…..Perhaps you have and I just haven’t found that yet.

    You write very clearly and I think perhaps I could follow what you write and adapt it to my situation. Most these “whiz-bang” VBA programms cannot write worth crap!
    Just my observations – their biggest, biggest mistake is assuming everyone is 100% fluent in all the VBA jargon they use, which we are not!! Ashamed that someone
    cannot make thier point clear because they are horrible at writing!!

    Most appreciate! If you’re a Dad – Happy Farther’s Day

    Mort Wakeland
    Dallas, TX


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.