Please note files for the “Hands-On” project may be found on the companion CD-ROM.
| Chapter 16 | USING DIALOG BOXES |
In Chapters 4 and 5, you learned how to use the built-in InputBox function to collect single items of data from the user during the execution of your VBA procedure. But what if your procedure requires more data at runtime? The user may want to supply all the data at once or make appropriate selections from a list of items. If your procedure must collect data, you can:
• Use the collection of built-in dialog boxes
• Create a custom form
This chapter teaches you how to display the built-in dialog boxes from your VBA procedures. In Chapter 17, you will design your own custom forms from scratch.
Before you start creating your own forms, you should spend some time learning how to take advantage of dialog boxes that are built into Excel and are therefore ready for you to use. I’m not talking about your ability to manually select appropriate options, but how to call these dialog boxes from your own VBA procedures.
Microsoft Excel has a special collection of built-in dialog boxes that are represented by constants beginning with xlDialog, such as xlDialogClear, xlDialogFont, xlDialogDefineName, and xlDialogOptionsView. These built-in dialog boxes, some of which are listed in Table 16.1, are Microsoft Excel objects that belong to the built-in collection of dialog boxes. Each Dialog object represents a built-in dialog box.
TABLE 16.1. Frequently used built-in dialog boxes |
|
Dialog Box Name |
Constant |
New |
|
Open |
|
Save As |
|
Page Setup |
|
|
|
Fonts |
|
To display a dialog box, use the Show method in the following format:
Application.Dialogs(constant).Show
For example, the following statement displays the Fonts dialog box:
Application.Dialogs(xlDialogFont).Show
Figure 16.1 shows a list of constants identifying Excel built-in dialog boxes, which is available in the Object Browser window after selecting the Excel library and searching for xlDialog.
Let’s practice displaying some of the Excel dialog boxes straight from the Immediate window.
|
Please note files for the “Hands-On” project may be found on the companion CD-ROM. |
FIGURE 16.1. Constants prefixed with “xlDialog” identify Excel built-in dialog boxes.
|
Hands-On 16.1. Using Excel Dialog Boxes from the Immediate Window |
1. Open a new workbook and save it as C:\VBAExcel2016_ByExample\Chap16_VBAExcel2016.xlsm.
2. Switch to the Visual Basic Editor window and open the Immediate window.
3. In the Immediate window, type the following statement and press Enter:
Application.Dialogs(xlDialogFont).Show
The above instruction displays the Fonts dialog box.
After displaying a built-in dialog box, you can select an appropriate option, and Excel will format the selected cell or range, or the entire sheet. Although you can’t modify the looks or behavior of a built-in dialog box, you can decide which initial setting the built-in dialog box will display when you show it from your VBA procedure. If you don’t change the initial settings, VBA will display the dialog box with its default settings.
4. Press Cancel to exit the Fonts dialog box.
5. In the Immediate window, type the following statement and press Enter:
Application.Dialogs(xlDialogFontProperties).Show
The above instruction displays the Format Cells dialog box with the Font tab active.
6. Press Cancel to exit the Format Cells dialog box.
7. In the Immediate window, type the following statement and press Enter:
Application.Dialogs(xlDialogDefineName).Show
The above statement displays the Define Name dialog box where you can define a name for a cell or a range of cells.
8. Press Close to exit the Define Name dialog box.
9. In the Immediate window, type the following statement and press Enter:
Application.Dialogs(xlDialogOptionsView).Show
The above instruction opens the Excel Options dialog box with the Advanced options displayed, as shown in Figure 16.2.
FIGURE 16.2. The advanced settings available in the Excel Options dialog box are identified by the xlDialogOptionsView constant.
10. Press Cancel to exit the Excel Options dialog box.
11. Type the following statement in the Immediate window and press Enter:
Application.Dialogs(xlDialogClear).Show
Excel shows the Clear dialog box with four option buttons: All, Formats, Contents, and Comments. Normally, the Contents option button is selected when Excel displays this dialog box. But what if you wanted to invoke this dialog with a different option selected as the default? To do this, you can include a list of arguments. Arguments are entered after the Show method. For example, to display the Clear dialog box with the first option button (All) selected, you would enter the following statement.
Application.Dialogs(xlDialogClear).Show 1
Excel often numbers the available options. Therefore, All = 1, Formats = 2, Contents = 3, Comments = 4, and Hyperlinks = 5. The built-in dialog box argument lists are available at http://msdn.microsoft.com/en-us/library/office/ff838781(v=office.15).aspx (see Figure 16.3).
12. Press Cancel to close the Clear dialog box.
13. To display the Fonts dialog box in which the Arial 14-point font is already selected, type the following instruction in the Immediate window and press Enter:
Application.Dialogs(xlDialogFont).Show "Arial", 14
FIGURE 16.3. Microsoft Excel built-in dialog box arguments list.
14. Press Cancel to close the Fonts dialog box.
15. To specify only the font size, enter a comma in the position of the first argument:
Application.Dialogs(xlDialogFont).Show , 8
16. Press Cancel to close the Fonts dialog box.
17. Type the following instruction in the Immediate window and press Enter:
Application.Dialogs(xlDialogDefineName).Show "John", "=$A$1"
The above statement displays the Define Name dialog box, enters “John” in the Names in workbook text box, and places the reference to cell A1 in the Refers to box. The Show method returns True if you click OK and False if you cancel.
18. Press Close to close the Define Name dialog box.
FileDialog is a very powerful dialog object. This object allows you to display the File Open and File Save As dialog boxes from your VBA procedures. Because the FileDialog object is a part of the Microsoft Office 16.0 object library, it is available to all Office applications. You can also use two methods of the Application object (GetOpenFilename and GetSaveAsFilename) to display File Open and File Save As dialog boxes without actually opening or saving any files (this is discussed later in this chapter). Let’s practice using the FileDialog object from the Immediate window.
|
Hands-On 16.2. Using the FileDialog Object from the Immediate Window |
1. To display the File Open dialog box, type the following statement in the Immediate window and press Enter:
Application.FileDialog(msoFileDialogOpen).Show
2. Press Cancel to close the File Open dialog box.
3. To display the File Save As dialog box, type the following statement and press Enter:
Application.FileDialog(msoFileDialogSaveAs).Show
4. Press Cancel to close the File Save dialog box.
In addition to File Open and File Save As dialog boxes, the FileDialog object is capable of displaying a dialog box with a list of files or a list of files and folders. Let’s take a quick look at these dialog boxes.
5. Type the following statement in the Immediate window and press Enter to display the Browse dialog box shown in Figure 16.4:
Application.FileDialog(msoFileDialogFilePicker).Show
FIGURE 16.4. The Browse dialog box lets users select one or more files. This dialog box displays a list of files and folders, and shows Browse in the titlebar.
6. Press Cancel in the dialog box to return to the Immediate window.
7. Type the following statement in the Immediate window and press Enter:
Application.FileDialog(msoFileDialogFolderPicker).Show
Excel displays a dialog box with a list of directories.
8. Press Cancel in the dialog box to return to the Immediate window.
The constants that the FileDialog object uses are listed in Table 16.2. The “mso” prefix denotes that the constant is a part of the Microsoft Office object model.
TABLE 16.2. FileDialog object’s constants |
|
Constant Name |
Value |
|
1 |
|
2 |
|
3 |
|
4 |
When you choose File | Open, Excel displays the Open dialog box listing all Excel files. You can control the types of files that are displayed in this window via the drop-down box located to the right of the File name drop-down box, or you can do this programmatically by using the Filters property. If the filter you need is not listed in the Open dialog, you can add it to the filters list. Filters are stored in the FileDialogFilters collection for the FileDialog object.
In the following Hands-On you will create a simple procedure that returns the list of default file filters to an Excel worksheet.
|
Hands-On 16.3. Writing a List of Default File Filters to an Excel Worksheet |
1. In the Project Explorer window, select VBAProject (Chap16_VBAExcel2016.xlsm).
2. In the Properties window, rename the project VBA_Dialogs.
3. Insert a new module into the VBA_Dialogs (Chap16_VBAExcel2016.xlsm) project and rename it DialogBoxes.
4. In the DialogBoxes Code window, enter the ListFilters procedure as shown below:
Sub ListFilters()
Dim fdf As FileDialogFilters
Dim fltr As FileDialogFilter
Dim c As Integer
Set fdf = Application.FileDialog(msoFileDialogOpen).Filters
Workbooks.Add
Cells(1, 1).Select
Selection.Formula = "List of Default filters"
With fdf
c = .Count
For Each fltr In fdf
Selection.Offset(1, 0).Formula = fltr.Description & ": " & fltr.Extensions
Selection.Offset(1, 0).Select
Next
MsgBox c & " filters were written to a worksheet."
End With
End Sub
The above procedure declares two object variables. The fdf object variable returns a reference to the FileDialogFilters collection of the FileDialog object, and the fltr object variable stores a reference to the FileDialogFilter object. The Count property of the FileDialogFilters collection returns the total number of filters.
Next, the procedure iterates through the FileDialogFilters collection and retrieves the description and extension of each defined filter.
5. Run the ListFilters procedure.
When the procedure completes, you should see a list of preset filters in the worksheet of a new workbook.
Using the Add method of the FileDialogFilters collection, you can easily add your own filter to the default filters. The following modified ListFilters procedure (ListFilters2) demonstrates how to add a filter to filter out temporary files (*.tmp). The last statement in this procedure will open the File Open dialog box so that you can check for yourself that the custom filter Temporary files (*.tmp) has indeed been added to the list of filters in the drop-down list.
Sub ListFilters2()
Dim fdf As FileDialogFilters
Dim fltr As FileDialogFilter
Dim c As Integer
Set fdf = Application.FileDialog(msoFileDialogOpen).Filters
Workbooks.Add
Cells(1, 1).Select
Selection.Formula = "List of Default filters"
With fdf
c = .Count
For Each fltr In fdf
Selection.Offset(1, 0).Formula = fltr.Description &": " & fltr.Extensions
Selection.Offset(1, 0).Select
Next
MsgBox c & " filters were written to a worksheet."
.Add "Temporary Files", "*.tmp", 1
c = .Count
MsgBox "There are now " & c & " filters." & vbCrLf & "Check for yourself."
Application.FileDialog(msoFileDialogOpen).Show
End With
End Sub
You can remove all the preset filters using the Clear method of the FileDialogFilters collection. For example, you could modify the ListFilters2 procedure to clear the built-in filters prior to adding the custom filter—Temporary files (*.tmp).
When you select a file in the Open File dialog box, the selected filename and path is placed in the FileDialogSelectedItems collection. Use the SelectedItems property to return the FileDialogSelectedItems collection. By setting the AllowMultiSelect property of the FileDialog object to True, a user can select one or more files by holding down the Shift or Control keys while clicking filenames.
The following procedure demonstrates how to use the above-mentioned properties. This procedure will open a new workbook and insert a listbox control. The user will be allowed to select more than one file. The selected files will then be loaded into the listbox control, and the first filename will be highlighted.
|
Hands-On 16.4. Loading Files into a Worksheet Listbox Control |
1. In the DialogBoxes module Code window, enter the ListSelectedFiles procedure as shown below:
Sub ListSelectedFiles()
Dim fd As FileDialog
Dim myFile As Variant
Dim lbox As Object
Application.FileDialog(msoFileDialogOpen).Filters.Clear
Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = True
If .Show Then
Workbooks.Add
Set lbox = Worksheets(1).Shapes.AddFormControl(xlListBox, Left:=20, Top:=60, Height:=40, Width:=300)
lbox.ControlFormat.MultiSelect = xlNone
For Each myFile In .SelectedItems
lbox.ControlFormat.AddItem myFile
Next
Range("B4").Formula = "You've selected the following " & lbox.ControlFormat.ListCount & " files:"
lbox.ControlFormat.ListIndex = 1
End If
End With
End Sub
The above procedure uses the following statement to clear the list of filters in the File Open dialog box to ensure that only the preset filters are listed:
Application.FileDialog(msoFileDialogOpen).Filters.Clear
Next, the reference to the FileDialog object is stored in the object variable fd:
Set fd = Application.FileDialog(msoFileDialogOpen)
Prior to displaying the File Open dialog box, we set the AllowMultiSelect property to True so that users can select more than one file.
Next, the Show method is used to display the File Open dialog box. This method does not open the files selected by the user. When the user clicks the Open button, the names of the files are retrieved from the SelectedItems collection via the SelectedItems property and placed in a listbox on a worksheet.
2. Run the ListSelectedFiles procedure. When the File Open dialog box appears on the screen, switch to the VBAExcel2016_ByExample folder, select a couple of files (hold down the Shift or Ctrl key to choose contiguous or nonadjacent files), and then click Open.
The selected files are not opened. The procedure simply loads the names of the files you selected in a listbox control that has been added to a worksheet (see Figure 16.5).
FIGURE 16.5. User-selected files are loaded into a listbox control placed in a worksheet by the ListSelectedFiles procedure.
If you’d like to immediately carry out the File Open operation when the user clicks the Open button, you must use the Execute method of the FileDialog object. The OpenRightAway procedure shown below demonstrates how to open the user-selected files right away.
Sub OpenRightAway()
Dim fd As FileDialog
Dim myFile As Variant
Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = True
If .Show Then
For Each myFile In .SelectedItems
.Execute
Next
End If
End With
End Sub
For many years now, Excel has offered its programmers two handy VBA methods for displaying the File Open and File Save As dialog boxes: GetOpenFilename and GetSaveAsFilename. These methods are available only in Excel and can still be used in Excel 2013 if backward compatibility is required. The GetOpenFilename method displays the Open dialog box, where you can select the name of a file to open. The GetSaveAsFilename method shows the Save As dialog box. Let’s try out these methods from the Immediate window.
Let’s open a file using the GetOpenFilename method.
|
1. Type the following statement in the Immediate window and press Enter:
Application.GetOpenFilename
The above statement displays the Open dialog box where you can select a file. The GetOpenFilename method gets a filename from the user without actually opening the specified file. This method has four optional arguments. The most often used are the first and third arguments, shown in Table 16.3.
TABLE 16.3. Arguments of the |
||
Argument Name |
Description |
|
|
This argument determines what appears in the dialog box’s Save as type field. For example, the filter excel files (*.xls), .xls displays the following text in the Save As drop-down list of files: excel files. The first part of the filter, excel files (.xls), determines the text to be displayed. The second part, .xls, specifies which files are displayed. The filter parts are separated by a comma. |
|
|
This is the title of the dialog box. If omitted, the dialog box will appear with the default title “Open.” |
|
2. Click Cancel to close the dialog box opened in Step 1.
3. To see how arguments are used with the GetOpenFilename method, enter the following statement in the Immediate window (be sure to enter it on one line and press Enter):
Application.GetOpenFilename("excel macro-enabled files(*.xlsm), *.xlsm"),,"Highlight the File"
Notice that the Open dialog box now has the text “Highlight the File” in the titlebar. Also, the Files of type drop-down listbox is filtered to display only the specified file type.
4. Click Cancel to close the dialog box opened in Step 3.
The GetOpenFilename method returns the name of the selected or specified file. This name can be used later by your VBA procedure to open the file. Let’s see how this is done.
5. In the Immediate window, type the following statement and press Enter:
yourFile = Application.GetOpenFilename
This statement displays the Open dialog box. The file you select while this dialog box is open will be stored in the yourFile variable.
6. Select an Excel file and click Open.
Notice that Excel did not open the selected file. All it did is remember its name in the yourFile variable. Let’s check this out.
7. In the Immediate window, type the following statement and press Enter:
?yourFile
Excel prints the name of the selected file in the Immediate window. Now that you have a filename, you can write a statement to actually open this file (see the next step).
8. In the Immediate window, type the following statement and press Enter:
Workbooks.Open Filename:=yourFile
Notice that the file you picked is now opened in Excel.
9. Close the file you opened in Step 8.
NOTE |
The |
Now that you know how to open a file using the GetOpenFilename method, let’s examine a similar method that allows you to save a file. We will continue to work in the Immediate window.
|
1. Open a new workbook and switch to the Visual Basic Editor window.
2. In the Immediate window, type the following statement and press Enter.
yourFile = Application.GetSaveAsFilename
The above statement displays the Save As dialog box. The suggested filename is automatically entered in the File name box at the bottom of this dialog box. The GetSaveAsFilename method is convenient for obtaining the name of the file the workbook should be saved as. The filename that the user enters in the File name box will be stored in the yourFile variable.
3. Type Test1.xlsx in the File name box and click Save.
When you click the Save button, the GetSaveAsFilename method will store the filename and its path in the yourFile variable. You can check out the value of the yourFile variable in the Immediate window by entering the following statement and pressing Enter:
?yourFile
To actually save the file you have to enter a different statement, as demonstrated in the next step.
4. In the Immediate window, type the following statement and press Enter:
ActiveWorkbook.SaveAs yourFile
Now the workbook file opened in Step 1 has been saved as Test1.xlsx.
5. To close the Test1.xlsx file, type the following statement in the Immediate window and press Enter:
Workbooks("Test1.xlsx").Close
NOTE |
Because the file we are working with does not contain any VBA code, we have saved it with the “xlsx” extension instead of using the macro-enabled file format (xlsm). |
When using the GetSaveAsFilename method, you can specify the filename, file filter, and custom title for the dialog box:
yourFile = Application.GetSaveAsFilename("Test1.xlsx", "Excel
files(*.xlsx), *.xlsx",,"Name of your file")
In this chapter, you learned how to use VBA statements to display various built-in dialog boxes. You also learned how to select files by using the FileDialog object. You ended this chapter by familiarizing yourself with older methods of displaying the File Open and File Save As dialog boxes that you will encounter often in VBA procedures written in older versions of Excel.
In the next chapter, you will learn how to create and display your custom dialog boxes with user forms.
