Using command tools such as buttons and drop down boxes to your Excel spreadsheet makes your application more professional and improvements productivity. It enables users to make selections, choose options and other related tasks directly in the spreadsheet.
The methods to add tools at run-time variants depending on which version of Excel you have this article shows you how to implement a command button in Excel 2010.
Some Examples of using a command button include:
- Taking the user to the last used row
- Opening other applications such as Word and Access
- Importing data from another Excel spreadsheet or text file
- Running the code to create a report
Using VBA To Insert A Command Button
Once you've decided to insert a command button in your spreadsheet save the following code in a general code module in the VBA window. The numeric values refer to the dimensions of the button which you can set to suit your application needs.
activeSheet.buttons.add (90,30,90,30) .select
selection.name = "myButton"
Although you can set the macro and caption in the initial code it's good to be able to access the button directly, instead of at creation time.For example you might need to run a different procedure depending on a particular user action.
dim bt as shape
set bt = activeSheet.shapes ("myButton")
bt.onAction = "BoldText"
bt.TextFrame.characters.text = "My caption"
In the example above, the macro "Boldtext" will run whenever the button is clicked and for our purposes we want every cell in column "A" to be in bold type.
You can place the macro anywhere in the code window.
Dim rng As Range
Set rng = ActiveCell.CurrentRegion.Columns (1)
For Each c In rng.Rows
c.Font.Bold = True
Although you can add a button manually and then set the macro it makes sense to do it at run time; that way the spreadsheet is reserved for data entry.
Another option is to have the button embedded in the spreadsheet which opens up a user form giving the user a wider range of options. While this gives more flexibility a single button is sometimes all that is required.
Adding command tools such as buttons and drop down boxes add professionalism to your Excel applications. How you can use command tools is limited only by your own imagination and creativity and the needs of your VBA project.