Excel 2019 Power Programming with VBA

CHAPTER 12Leveraging Custom Dialog Boxes

IN THIS CHAPTER

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

Alternatives to UserForms

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. UserForms are one way for Excel developers to create custom dialog boxes. However, VBA provides alternative methods to display built-in dialog boxes with minimal programming required.

Before we get into creating UserForms (beginning with Chapter 13, “Introducing UserForms”), 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 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. We explain each method in the sections that follow.

Using the VBA InputBox function

The syntax for VBA's InputBox function is as follows:

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile, ...