On-the-fly data entry form

Hi all,

So after Dicks (nice to read) Alive and Well , let’s do some Excel/VBA stuff again.

I intend to get a bit of discussion on this one, so bear with me.

I’m currently developing an Excel workbook for a customer. One of the interesting things with this project is that it consists of multiple data tables, each in its own worksheet. The customer needs to be able to edit the data in these tables.

One of these might contain these fields:


The way I would normally have done this is by creating a userform with a control for each field and all the coding that is needed to handle record selection and stuff. (and yes, I do know there is MS-Access :-))

But since I have an odd 5 worksheets to handle I decided it would be nice to have a generic data entry form that would build itself using a companion worksheet for each data sheet.

For each worksheet that requires data entry I inserted a companion sheet with this information:


My VBA code inside the userform’s code module reads this sheet and builds the controls accordingly.
The form has a couple of properties I can set to control appearance. All it now takes to show the data entry form for worksheet “oSh” is this bit of code:

Set frmDataEntry = New ufDataEntry
With frmDataEntry
Set .Source = oSh
Set .SourceSettings = ThisWorkbook.Worksheets(oSh.Name & “_Fields”)
.Title = sTitle
.RowCount = 14
.FieldWidth = 120
.LabelWidth = 150
.Label2FieldMargin = 12
.VertMargin = 3
.HorMargin = 6
.CurrentDataRow = 1

I’ve got this all up and running, including Validation and all (and yes: I’ll be writing this all up in detail some day).

Now to the questions of the day:

What do you think about the method I chose?
What alternative solutions have you come up with in the past?


Jan Karel Pieterse

Posted in Uncategorized

14 thoughts on “On-the-fly data entry form

  1. Jan:
    I did something similar but I used a multipage control. Each page of the multipage have other controls linked to specific cells and linked to a specific worksheet.

  2. jk i have ribons of data 170 carecters ling eg aaa4der78dre345nnm8…….i need to split it in excel in to eg a1: aaa a2:4 b1:der b2:78 …….how can i do this as the code lenths are not konstant. pleas help sam

  3. Out of curiosity, are any of the data fields related to other data tables? If so, how do you plan to handle anomalies?

    I’ve typically used methods similar to yours in the past, but lately all my projects seem to be better modeled using relational tables in Access. For someone who worshipped Excel for about three years and nearly swore off anything else, I’ve been quite impressed by Access’ capabilities. BTW, I can’t reccommend enough the Access 2002 Developer’s Handbook series.

  4. Hi Matthew,

    Indeed there are relationships. For now I only pull data from other sheets to fill comboboxes.

    The columns labelled “tied to other sheet/list”, “IdColumnName and “NameColumnName” control what content is used for the comboboxes.

    If the column called “tied to other sheet/list” contains a string with a semi-colon the code assumes the cell contains a list spearated by semi-colons.

    Otherwise the code uses the indicated sheetname and looks up the index in the IdColumnName and uses the value in NameColumnName. Clear as mud?

  5. Matthew, a 2nd to your recommendation. I’m just discovering the joys of Access (just split my first database). The Ken Getz, et. al. Desktop Developer’s Handbook has been very helpful.

  6. I agree Access is by far better for relational databases. And in fact, the backend is a database. But there are specific reasons to stick with Excel for now.
    I’ve been developing a lot in Access lately.

  7. I don’t see any advantage in creating forms ‘on the fly’. Or am I missing something ?
    Felipe’s suggestion makes sense to me, especially when the ‘Style’-property has been set to 2-fmtabstylenone. The pages appear to the user as different userforms although they are not.
    Depending on the sheet that has to be filled the corresponding page (tab) will be activated.
    The way the data is written into the worksheet can be controlled in accordance to the active page of the multipage control.

  8. Jan Karel,

    I, too, have been finding very specific reasons for staying with Excel. Particularly, the manager’s fear of the unknown. Despite the ability to collect, store, analyze, and report data within a single, multi-user application, my coworkers do not see the benefit of a database in many applications. Frustrating at times… But, I won’t get the thread too far off its original topic.

    I might have used a multi-page control, but I like the flexibility of table-driven code. If you can handle the relational data accurately, I like the strategy.

  9. I think that once you have more than, say, two such tables, you’re well-advised to go for the single dynamic userform. (Actually, I’d seriously consider it at two).

    Suppose you need to make some look-and-feel change, for example.

    It keeps the whole thing DRY (http://en.wikipedia.org/wiki/Don%27t_repeat_yourself) which is generally a Good Thing.

    Also, it’s more interesting to make one form smart than to make ten dumb forms!

  10. I’ve been trying to do something like this, but I’ve come up against a problem – I can’t set events on the controls that have been created on the fly. [I want a textbox to update when the value in another textbox is changed]. I’m thinking that I might be able to use a class to do this, but am I missing something simpler and more obvious?

  11. Thanks. I saw that in one of your earlier posts, but I can get away with using the events that are available. Actually I don’t really need to do it at all, the form’s working fine without those boxes updating, and if I really needed them to update I could just go with building the form at design time. But it’s going to make the form look better and I’m with Mike above: “It’s more interesting…”

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

Leave a Reply

Your email address will not be published.