ShowDataForm

Launch Excel's Hidden Dynamic Form from VBA

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

BXL Tiny Database Add-in

Excel's dynamic form inspired us to create our own. But we didn't just reinvent the wheel. At bottom is an sample and at right is a comparison between Beyond Excel's Dynamic Form and Excel's. As you can see, Beyond Excel's version is pact with features which we realized made it a perfect front end for Excel databases. That in-turn, inspired us to create BXL TIny Database add-in available at Eloquens.

Page updated

Google Sites

Report abuse