Dynamic Forms
Post date: May 15, 2014 6:32:39 PM
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
etc.
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 started using frmData.Display(). which takes as input a range object and, optionally, a title string. The range object must contain cells in a table. frmData.Display() passes the range to frmData.AddControls() which:
Determines the table's maximum column width to use as the new controls' widths
Uses the form's method Controls.Add() to create:
Label controls to display each column's heading
Label controls to display each locked cell's value
Textbox controls to display and allow changes to unlocked cells
Combobox controls to display and allow changes to unlocked cells with list data validation type
Adjust the form's overall dimensions and repositions OK and Exit buttons as well as a special textbox reserved for messages to the user such as data validation input messages (see Enhancement #1) and error messages.
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 grey 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.
Final Result
DynamicForm.xlam is an XL 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. It's all free and unprotected. It is my hope people use this to improve their skills. Here is a PDF explaining everything:
