Add Forms to Edit Tables

Why read this:

• • Learn how to add a user entry form to any table in seconds

Back in XL2003, when "Lists" (which later became "Tables") were introduced, there was a menu option that presented the "List" in a form. With this form we could add records, delete them, change them, and search for them. And a really neat feature of the form was that any data validation already in our lists worked in the form. If you have XL2003, you can see this by creating any "List" (select a range of data and press Ctrl-L to turn it into a "List'), and then find the "List" dropdown in the menu bar. In that dropdown is the option "Form". Click it and the form appears.

In XL2007 "Lists" became "Tables" and the "Form" menu option disapeared. I assumed the feature was removed. I assumed wrong. I found it again and in doing so learned this wasn't a new feature for "Lists". This feature has been around for quite a while and implementing is just slightly more difficult than in XL2003.

The most obvious way is to add this feature to the "Quick Access Toolbar" (QAT). Use Excel Options > Customize. In the "Choose commands from" dropdown select "Commands not in the Ribbon". Scroll down the command list and find "Form...". Double click it then click OK to return to the worksheet. Your QAT now has a new icon. To use it, create any list (it doesn't have to be a table), place your cursor anywhere in the list, then click the new icon. That's it!

The QAT is great for personal use, but if you create solutions for others like I do, we shouldn't modify the users' QAT. Instead we can use VBA to add these forms to our applications with very few lines of code and with either of two methods.

This first method requires we name our list, "Database" and make the sheet that contains that name active. Once done we can use the command: Activesheet.ShowDataForm.

The second command is more cryptic but avoids the restriction of naming the range "Database" which tends to limit us to having just one table that we can use the form with. Simply select any cell in any list and use this command: Application.CommandBars.FindControl(, 860).Execute

Adding this command to the worksheet double click event gives users a quick and easy way to bring up a data entry form for any list on the worksheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Target.CurrentRegion.Select

Application.CommandBars.FindControl(, 860).Execute

End Sub