Microsoft Office Tutorials and References
In Depth Information
Getting Users to Make Decisions
Case 7 To 9: MsgBox “Quarter 3”
Case 10 To 12: MsgBox “Quarter 4”
End Select
End Sub
As you can see, you don’t need 12 separate statements to handle each conditional month; you can
simply state the range of months using the To statement in each Case . I put a new wrinkle in that
macro to point out a VBA feature, that being the colon character ( : ), which can be used to separate
multiple statements on the same line that would otherwise each require their own line. I don’t
usually use the colon character this way, but sometimes it comes in handy by helping the readability of
small macros like this.
gETTing usErs To MAkE dEcisions
Thus far you have seen examples of VBA’s decision-making abilities that have not required any input
from the user. The time will come when you’ll either want or need information from the user in
order for decisions to be made that only the user can provide. Message boxes and InputBoxes are
excellent tools to interact with your users in such situations.
Message Boxes
Up to this point in the book, you have seen many examples of code that include a message box. In
all those examples, the message box was a simple pop-up box that displayed an informational text
message, with an OK button for you to acknowledge the information.
Message boxes are flexible tools that allow you to customize the
buttons while asking questions directly to the users that will force
them to select one option or the other. Instead of OK, you can
display a Yes button and a No button on your message box, and
write the code that will be followed if the user clicks Yes, or the
user clicks No. An example of such a message box is shown in
Figure 8-1.
figurE 8-1
Say you have a macro to perform a task that your users should confirm they really want to do as a
final OK. Some macros are quite large and virtually irreversible, or the task at hand will alter the
workbook in a significant way. In the following simplified example, the active worksheet will be
copied and placed before Sheet1, but only if the user first clicks the Yes button to confirm his intention for
this to happen. If the user clicks No, a friendly message box will advise the user that the macro will
not run because No was clicked.
Sub ConfirmExample()
Select Case MsgBox( _
“Do you really want to copy this worksheet?”, _
vbYesNo + vbQuestion, _
“Please confirm...”)
Search JabSto ::




Custom Search