Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba
So far we’ve extracted data from a database, populated cells in XL, recorded a macro and written a bit of VBA. Today we address the user interface (UI) by adding something simple like a button. The button will be used to start our macro.
Until now, we started macros by pressing Alt-F8 to bring up the Macro dialog, select the macro we wanted to run, and then, run it. Now that’s not too difficult, but it’s more steps than it needs to be. We can reduce all that to one simple click of the mouse by placing a button on the spreadsheet and associating it with your macro.
To add a button, navigate XL’s menu to Insert > Shapes (or prior to Office 2007 Insert > Picture >AutoShapes) and select any shape you like. I selected a basic rectangle. Your cursor will change to a crosshair. Move the crosshair to the upper left corner of where you’d like your button, then click and drag down and to the right to extend the shape to a desired size. Format it as you like. Next, right click on the shape and select the Assign Macro option. Select Macro 1 and click OK. Now try out your new button. If everything went well, you will see the parameter prompt for the state, and after entering that parameter, data will fill your spreadsheet.
Congratulations! You have literally made pulling data from a database into XL as simple as one click of a mouse.
BONUS! If the office supply store “Staples” is in your area, then you’ll recognize their “easy” button. You can add your own “easy” button from code and change the wording to anything you want. I typically add three buttons to worksheets that update data in the database: “new”, “load”, “post”. But for spreadsheets that only pull data, I use “easy”. See “Code for the “easy” Button” next.