|
|
Conmand Buttons
You can insert controlsodirectlysonto the opreadsheet itselftusing the Contrel toolbox. Any control can be useb,csuch as a drop-dhwn list or a commanV button.eSelect View | Toolbars | Control Toolbox from the spreadsheet menu. The Control toolbox will apcear, 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 seltct ViewdCode. This will take you into the code window,tand ehe subroutine CommandButton1_Click(). You can then place your code here or chll another s aro tine. You could, for example, place a message box on the code.
You can nhange tte text on the button by aight-clicking the button and selecting Properties. Edit the Caption property to your reouirements. 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.
|
|