Data Entry Form Enhancements

One of the most popular downloads at my Web site is the J-Walk Enhanced DataForm v2. It’s an add-in that provides an alternative to Excel’s built-in Data Form (accessed with Data – Form). Personally, I think the most efficient way to enter data into a worksheet is to do it directly. But, for some reason, many seem to prefer using a form.

I’m going to make an Excel 2007 version of this add-in, and I thought I might add a few new features while I’m at it. Besides a new UI, I’m going to try to incorporate Andy Pope’s clever Resizable UserForm technique.

Any other suggestions?

By the way, Excel’s built-in data entry form is not present in the Excel 2007 UI. To use the feature, you must customize your Quick Access Toolbar and add theForm command from the Commands Not in the Ribbon group.

Posted in Uncategorized

20 thoughts on “Data Entry Form Enhancements

  1. I always had trouble getting lookup formulas to work with the form. e.g. a data table where the rightmost column looks up an ID based on the name selected in the drop-down box for the Name field in the form. Could have been user error though.
    I love your form, I’ve used it quite a bit building solutions for companies that don’t want users to touch the data except the initial data entry.

  2. Brainstorming here:
    Worksheet Selector to dynamically select the worksheet that feeds the form.
    OnDirty indicator (somehow highlight the records that have been touched)
    Send $1 to the pay pal account of mha105@yahoo.com each time a user hits the Next button

  3. Personally, I like to do data entry directly in the worksheet, too. So I’m not sure this is worth doing???

    Some way of “copy from previous row” or “copy to next row”. I’m not sure that this should be a field by field entry (a checkmark that looks to see if the field if the field is empty, then fills in the previous row’s entry???).

    Maybe it’s not worth it…

    Maybe a pick from list type feature that shows all the other entries in that field would be better????

  4. I find the Criteria pane a little confusing, it’s not obvious what it does and when. Also, that would be a cool place to toggle Autofilter using the criteria shown.

  5. I was wondering if it would be possible to add a lable and a corresponding edit box on the fly once a user clicks on “add new Field” button….I am not sure if this is possible without hardcoding the maximum number of controls to add

    Sam

  6. Thanks for all of the suggestions.

    Dave, the “copy from” idea might be useful. I’ll see if I can implement it.

    Harald, excellent suggestion. One problem is that the criteria boxes in the form aren’t nearly as flexible as the autofilter criteria. In any case, I need to revisit how this works with filtered lists.

    Sam, It MIGHT be doable but it I’d say it’s not worth the effort. The alternative manual approach is much simpler: the user clicks Cancel, types a new column header, and then re-opens the UserForm.

  7. If I need a resizable UserForm I call ForceActiveWindowResize from the UserForm_Initialize. See below…

    Declare Function GetForegroundWindow Lib “user32? () As Long
    Declare Function SetWindowLong Lib “user32? Alias “SetWindowLongA” (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Declare Function GetWindowLong Lib “user32? Alias “GetWindowLongA” (ByVal hwnd As Long, ByVal nIndex As Long) As Long

    Public Sub ForceActiveWindowResize()
    Const GWL_STYLE = (-16)
    Const WS_THICKFRAME = &H40000
    Const WS_MAXIMIZEBOX = &H10000
    Dim hwnd As Long
    Dim style As Long

    hwnd = GetForegroundWindow()
    style = GetWindowLong(hwnd, GWL_STYLE) Or WS_THICKFRAME Or WS_MAXIMIZEBOX
    SetWindowLong hwnd, GWL_STYLE, style
    End Sub

  8. Oops, meant the UserForm_Activate. You can also simulate the resize event. See below…

    Private Sub UserForm_Activate()
    ForceActiveWindowResize
    UserForm_Resize
    End Sub

  9. Having created a form – I would like it to be abe to show data within an excel spreadsheet – i would like the user to be able to request a specific ID number – which is in Row A and then for the data to come up in particular fields within my form.
    I would also like for them to be able to click on a button at the end of the form which can print of the form.

    Any help and guidance or tutorial instructions are greatly appreciated

    Thank you

  10. John, like youself and many others, I prefer to enter data directly on the worksheet. However, your Enhanced DataForm is a useful utility that I’ve recommended to others. Here are some observations and suggestions:

    1) As implemented, the criteria pane is confusing and unintuitive.
    2) The utility blows away any data validation set for the columns. In many respects, this behavior undermines the use of the form. As a modification, you can have validation criteria set up directly in the form (in a global options tab perhaps). As a minimum, at least respect any validation criteria previously set.
    3) For columns identified with a validation criteria of “Date”, add a date picker option for data entry.

    Regards, Colin

  11. Hi, John… Looks great but haven’t seen the Excel Data Form field number limitation addressed. Believe it maxes out at 13? 2007 may have fixed that? Haven’t checked that out. How many fields does your data form support? If more, it is a super alternative to the native form.

    Thanks much!

    Gayle

  12. Excel 2007’s data form still maxes out far too low for my 100+ column tables.

    I’d like the ability to customize different forms for different users; selecting which column fields are available in each user’s form. Also, the ability to select some non-entry columns which contain formulas and have their values update as the fields of the record are filled out by the user.

    Copy-Insert n duplicates of this record would be handy.

    If a table field has conditional formating, display this formatting it in the data form field as well.

    Conditional user form fields whereby if a column contains a certain value, specified data form fields are enabled or disabled according to a conditional rule.

    How is the Excel 2007 version coming along John?

    cheers!
    -jason

  13. Thanks for this. Exactly what I’ve been trying to do in off-hours, and would never have done it half as well.

  14. This is indeed a good tool to use. I keep on searching the net to improve efficiency and speed of the data entry work. Excellent work. I will keep on checking your site for updates.


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

Leave a Reply

Your email address will not be published.