Excel 2016 Power Programming with VBA

12 Leveraging Custom Dialog Boxes

In This Chapter

• Using an input box to get user input
• Using a message box to display messages or get a simple response
• Selecting a file from a dialog box
• Selecting a directory
• Displaying Excel’s built-in dialog boxes

Before You Create That UserForm . . .

Dialog boxes are a key user interface element in many Windows programs. Virtually every Windows program uses them, and most users have a good understanding of how they work. Excel developers implement custom dialog boxes by creating UserForms. However, VBA provides the means to display some built-in dialog boxes, with minimal programming required.

Before I get into the nitty-gritty of creating UserForms (beginning with Chapter 13), you might find it helpful to understand some of Excel’s built-in tools that display dialog boxes. The sections that follow describe various dialog boxes that you can display using VBA, and without creating a UserForm.

Using an Input Box

An input box is a simple dialog box that allows the user to make a single entry. For example, you can use an input box to let the user enter text or a number or even select a range. You can generate an input box in two ways: by using the VBA InputBox function and by using the InputBox method of the Application object. I explain each method in the sections that follow.

The VBA InputBox function

The syntax for VBA’s InputBox function is

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile, context])

• prompt: Required. The ...