Want a data entry form that creates itself?
Forms make presenting and changing information in remote tables possible. Dynamic Forms make creating those forms effortless. Read on to learn how to create forms that design themselves to accommodate any XL table complete with data validation. The user just selects a row, presses SHIFT-CTRL-D, and the form creates itself instantly.
Why I Did This
I created an app to design my kitchen. The app uses XL’s shapes to draw floor plans and cabinet positions. Each shape had to be precisely drawn and positioned which required entering dimensions into a table. But having the table on the drawing’s worksheet was clumsy and ugly. I needed to work with shapes on one worksheet and enter dimension in a table on a different worksheet at the same time. That requires a form.
Options 1 – Create UserForm Manually
I considered creating a form the old fashion way:
- manually adding labels and text boxes
- setting each control’s properties
- coding routines to move data between the table and the controls
- coding validation routines
For the particular table I was working with, that meant 21 labels, 20 text boxes, 1 combo box, and 2 command buttons. That seemed like a lot of duplicate work considering the table had the data validation already.
Option 2 – Use XL’s List Form
Since 2003, XL has had an option to create dynamic forms over lists using a single line of code (see: Add Forms to Edit Tables). Unfortunately XL’s dynamic form has two shortcomings: it only works with the active worksheet and it can’t start on a specified table row.
Option 3 – Create a Dynamic Form
I wanted a method that:
- created the form for me using the table’s data validation, column headings, and cell protection
- worked over tables not necessarily on the same worksheet
- worked only on rows I specified.
Solution – frmData
frmData is a blank user form with:
- OK and Exit command buttons
- a textbox for user messages
- code to add all other controls based on the table sent to it.
frmData is really all I needed but I wanted more.
Enhancement #1 – clsInpMsg
I also wanted the data validation’s input message displayed when editing a table’s cell. For that I created class clsInpMsg. clsInpMsg responds to labels, textboxes, and combo boxes being selected or the mouse hovering over them and puts the associated cell’s data validation input message in frmData’s user messages textbox.
Enhancement #2 – clsForm
I also wanted the form to be a little more dynamic and fun than the standard drab gray user form. So I included class clsForm. clsForm adds a worksheet’s theme colors and “glowing” effects for command buttons when the mouse hovers over them. If you want normal, just remove two lines from frmData and (optionally) remove clsForm.
DynamicForm.xlam is an Add-In with all forms and classes assembled together for easy workbook integration.
AddInDemo.xls demonstrates the DynamicForm Add-in and shows how to dynamically install/uninstall add-ins when projects open/close.
Want it? Get it.
Here is a PDF explaining everything: DynamicForm.PDF