|
|
|
|
|
|
|
Discover
even more VBA for Excel tips and ideas |
| Lesson 8: Excel VBA Controls Command buttons In the Chapter on EVENTS (7) I have told you that 99% of my VBA procedures are triggered by a click on a button. And to make things simple I do not use VBA command buttons they are uselessly complicated and limited in how you can shape them. No VBA, I work in Excel with text boxes most of the time but also with images and WordArt. Download one of these buttons (right click on it in your browser and choose "Save image as"). Save it on your desktop:
Now open the workbook that you have created "VBATest1.xls" and select the sheet "Test2". Insert the image that you have imported anywhere "Insert/Picture/From File/Desktop/..........gif". Once the image has been added to the sheet, right click on the image and select "Assign Macro and select the macro "proTest2" that you have created earlier. Erase all the values in column C or the sheet and click on the button. So you can "borrow" all kinds of buttons from the Internet or create your own and use them as triggers for your VBA procedures. Once a button (image, word art or text box) has been assigned a macro or an hyperlink you need to select it with a right click to modify it. Most of the time I use text boxes that I create in Excel from the drawing toolbar (the first 4 buttons above). I can use the font that I like and the background color that fits my needs. If you are a little creative you can add 3D effects, special borders and the likes. A few note on Excel text boxes: I always keep the drawing toolbar at the bottom of my screen
You
create text boxes by a left click on the icon
|
|||||||
|
Drop-Down Lists Here again I stay away from VBA and develop my drop-down lists in Excel. If you need drop-down lists in 50 cells to help users select values you need to create 50 drop-down lists one by one. If you use the drop-down list functionality in Excel you create one in the first cell and copy/paste it in the other 49 cells. See how to do it on the page on Excel drop-down lists. |
|||||||
|
VBA Command Buttons If you insist on using VBA command buttons here is how you create and maintain them. First activate the VBA toolbar "View/Toolbar/VBA" (I always keep the VBA toolbar visible). Then
click on the toolbox icon
Left
click on the command button, move to the sheet, left click, hold
and stretch. Right click on the command button and chose "Properties".
Set the properties. Right click again and select "View Code".
Write the code. Finally, click on the design icon The main reason for which I never use VBA command buttons is that you cannot test code step by step within a command button but you can do it in a regular macro in a module, the macro that is assigned to an image a wordart or an Excel text box. |
|
|
, you then go to the worksheet and you left click hold and stretch
the text box. When the border of the active text box is made of
diagonal lines you can work the text. If you click again on the
border it becomes a set of dots and you can work the text box itself.
Right click on the border in any of the two states and you will
see that the menus are different. It is in the second state
that you can assign a macro to the text box.
.
to see the controls that you can use. Here is the toolbox:
on the VBA tool bar to activate the command button and close the
toolbox. To modify the command button you need to right click on
it then click on the design icon on the VBA toolbar and call the
properties or code window.