Beyond Excel: VBA and Database Manipulation

May 15 2014   1:43PM GMT

Creating Dynamic Forms -or- Forms that Create Themselves

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker


Want a data entry form that creates itself?

Dynamic Form

Dynamic Form

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
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.

Final Result
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


2  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • koitaki
    Hi Craig, is the DynamicForms.PDF link broken?
    0 pointsBadges:
  • Craig Hatmaker
    It appears to be working fine. The link points to a Dropbox account and Dropbox is blocked by some companies. If you are trying the link from work then that might be the case. Try it from home. If it still doesn't work send me a message at Craig_Hatmaker at Yahoo dot com.
    1,860 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: