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.
Gretchen:
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.
27 July 2007, 12:22 pmI 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.
Dick Kusleika:
How about comboboxes as fields?
27 July 2007, 1:37 pmMike Alexander:
Brainstorming here:
27 July 2007, 2:07 pmWorksheet 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
John Walkenbach:
It already supports drop-down boxes for fields, Dick.
27 July 2007, 5:24 pmDave:
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????
27 July 2007, 5:57 pmHarald Staff:
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.
28 July 2007, 3:54 amsam:
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
28 July 2007, 8:13 amJohn Walkenbach:
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.
28 July 2007, 12:14 pmjonnybgood:
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()
29 July 2007, 8:14 pmstyle = GetWindowLong(hwnd, GWL_STYLE) Or WS_THICKFRAME Or WS_MAXIMIZEBOX
SetWindowLong hwnd, GWL_STYLE, style
End Sub
jonnybgood:
Oops, meant the UserForm_Activate. You can also simulate the resize event. See below…
Private Sub UserForm_Activate()
29 July 2007, 8:19 pmForceActiveWindowResize
UserForm_Resize
End Sub
Excel Training Boise » Blog Archive » Do you use Excel for Data-Entry?| Microsoft Excel Classes & Training Seminars in Boise, Idaho | Free Tips!:
[…] you do, read about a way to use a great userform- a free add-in I have used and recommended many […]
30 July 2007, 11:50 pmNureen Lakhani:
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
31 July 2007, 8:55 amDick Kusleika:
Nureen, try here http://www.dailydoseofexcel.com/archives/2004/10/15/linking-userforms-and-worksheets-part-vi/
31 July 2007, 2:10 pmColin Banfield:
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
3 August 2007, 9:13 amGayle Larson:
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
8 August 2007, 10:06 amJason Cobb:
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!
23 August 2007, 9:06 pm-jason
CQE:
Thanks for this. Exactly what I’ve been trying to do in off-hours, and would never have done it half as well.
6 September 2007, 2:05 am