Excel VBA Macro Programming

You can insert controls directly onto the spreadsheet itself using the Control toolbox. Any control can be used, such as a drop-down list or a command button. Select View Toolbars Control Toolbox from the spreadsheet menu. The Control toolbox will appear, just as it does on a UserForm within the VBA editor.

Select the Command Button icon by clicking it (look at the tooltip text on each icon if you're unsure which one it is) and drag it onto the spreadsheet, as shown in Figure 11-2.

Figure 11-2: Placing controls directly onto a spreadsheet

Right-click the command button and select View Code. This will take you into the code window, and the subroutine CommandButton1_Click() . You can then place your code here or call another subroutine. You could, for example, place a message box on the code.

You can change the text on the button by right-clicking the button and selecting Properties. Edit the Caption property to your requirements. The Caption property will read CommandButton1 by default. You can change this to read MyButton or whatever title you wish to give it.

When you complete your button, exit design mode on the Control toolbox by clicking the icon in the top-left of the window. When you click it now, it is no longer in design mode and will run instead of having handles round it. Your button is now ready to run. Click it to see it run your code.

You may wish to make further amendments to the button or even delete it completely if you are not happy with it. The only problem now is that every time you click it, it runs the code, and right-clicking does nothing because Excel VBA does not interpret the right mouse click on a command button! What you need to do is to put the Control toolbox back on screen by again selecting View Toolbars Control Toolbox. Click the Design Mode icon in the top left of the toolbox window and you'll return to design mode. You can now select the button, resize it, delete it, and so on.

You can change the code behind the button without having to go into design mode. The code is completely separate from the spreadsheet itself. The code appears on the sheet object module for that particular sheet and it can be easily edited.