Beyond Excel: VBA and Database Manipulation

Oct 6 2009   7:03PM GMT

Improving the User Interface – Adding a Button

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Adding Buttons

Adding Buttons

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.

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

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: