Page 747 - Excel 2013 All-in-One For Dummies

Using the Visual Basic Editor 729
or an attribute is being reset by turning it on or off by setting it equal to True or False, such as
Selection.Font.Bold = True
to make the text in the current cell selection bold.
Now, it doesn’t require a programming degree (at least, not the last time I checked) to get the bright idea that you can make your macro behave differ- ently just by — carefully — editing these settings. For example, suppose that you want the final font size to be 24 points instead of 12. All you have to do is change
.Size = 12
to
.Size = 24
Likewise, you can have the macro apply single underlining to the cell selec- tion by changing
.Underline = xlUnderlineStyleNone
to
.Underline = xlUnderlineStyleSingle
Getting macro input by adding a dialog box
One of the biggest problems with recording macros is that any text or values that you have the macro enter for you in a worksheet or chart sheet can never vary thereafter. If you create a macro that enters the heading “Bob’s Barbecue Pit” in the current cell of your worksheet, this is the only heading you’ll ever get out of that macro. However, you can get around this inflex- ibility by using the InputBox function. When you run the macro, this Visual Basic function causes Excel to display an Input dialog box where you can enter whatever title makes sense for the new worksheet. The macro then puts that text into the current cell and formats this text, if that’s what you’ve trained your macro to do next.
To see how easy it is to use the InputBox function to add interactivity to an otherwise staid macro, follow the steps for converting the Company_Name macro that currently inputs the text “Mind Over Media” to one that actually prompts you for the name that you want entered. The InputBox function uses the following syntax:
InputBox(prompt[,title][,default][,xpos][,ypos] [,helpfile,context])
Book VIII Chapter 2
VBA Programming