|
Custom Command Bar Button
By D. C. Conlin
Add a custom button on the command bar to run your custom VBA code.
Access database developers often customize their applications to make the applications more efficient and more useful for their users. Creating a custom button on a command bar for time-consuming, repetitive tasks can be a real time saver. For example, the user may want to automate e-mailing a report to all managers in an organization. Instead of manually opening the report, saving it as a Snapshot file, creating a new e-mail and assigning the list of recipients, then attaching the Snapshot file and sending the e-mail to those recipients, the user can just click on a custom button on the command bar, and all of these steps will automatically be executed.
When creating a custom command bar, one normally assigns a macro to each button, but that's pretty limiting for someone who needs the customization of a user-defined procedure. Here's how to create a custom command bar button without any macros:
First, create a new module.
In this module, create a new public function for the custom command bar button to execute. This function must be a public function. (It cannot be a subroutine.) If you have already created a subroutine in a standard module or in a form that needs to be automated, then have this new function call that public subroutine or public function in your form. If your current code has this procedure on your form assigned as "Private" (which is the default), then you will have to change it to "Public." Download the example file CmdBar.zip (Access 2000) for code examples on calling a subroutine or function on a form from a module or another form.
Next, create a new command bar:
1. Right click on the built-in menu bar or command bar at the top of the Access screen.
2. Click on "Customize..." on the popup menu to open the "Customize" dialog window.
3. Click on the "Toolbars" tab.
4. Click on the "New..." button.
5. Type in the name of your new toolbar and click on the "OK" button. A new (empty) command bar will appear near the middle of your screen.
6. Click on the "Commands" tab in the "Customize" dialog window.
7. On the right hand panel of the "Customize" dialog window, you'll see a list of icons for the "File" menu. At the top of the list, you'll see "Custom" spelled out, but without an accompanying icon. Click on the word "Custom" and drag it over to your new command bar and drop it there. The word "Custom" appears on your new command bar, and it will be selected, i.e., it will have a heavy black line surrounding the button.
8. Click on the "Modify Selection" button near the bottom of the "Customize" dialog window.
9. On the popup menu, select "Properties" to open the "Control Properties" dialog window.
10. In the "Caption" text box, type in whatever you want to name your new button. When you click on the next text box (don't hit the <ENTER> key just yet, or you'll close the dialog window prematurely), your new caption changes the text on your new button and in the "ScreenTip" text box in the "Control Properties" dialog window.
11. You can leave the screen tip as is, or change it if you'd like.
12. In the "On Action" text box, this is normally where you would type the name of the macro that you want to execute. Instead, type in an equals sign, then the name of the public function that you defined in the module earlier, then open and closed parentheses. Example:
=MyCoolFunc( )
13. In the "Style" combo box, select "Default Style" to show only an icon, or select one of the other choices as you desire.
14. Click the "Close" button on the "Control Properties" dialog window to close it.
15. To create an icon for the button, click on the "Modify Selection" button near the bottom of the "Customize" dialog window. Then click on "Change Button Image" and select one of the icons.
16. Add more custom buttons to the command bar by repeating steps 7 through 15 as many times as needed.
17. When you are finished adding buttons to your command bar, click the "Close" button on the "Customize" dialog window to close it.
18. Drag your new toolbar to the top of the screen and dock it wherever you like.
19. Click on a form to watch the menu bar and command bars rearrange themselves to provide the menu and command bar buttons needed to operate on the form that is now active. Notice that your new command bar is now sitting in an awkward place, so you'll have to move it to a more aesthetically pleasing location.
20. Click on the database window and watch the menu bar and command bars rearrange themselves to provide the menu and command bar buttons needed to operate on the database window. Again, notice that your new command bar is sitting in an awkward place. Try to put your new command bar in a place that you can live with no matter what arrangement Access decides to put your menu bar and command bars in.
For a bonus, an example of how to programmatically enable and disable the command bar buttons is demonstrated in the attached file which provides this functionality when the user clicks from one text box to the other on the form. (It's a little yellow happy face that represents the new command bar and button.)
You may download the sample database: CmdBar.zip (Access 2000)
You can read about your favorite expert contributors.
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
 Top
Visitors since 4 Dec. '05: 
|
