Excel VBA Macro Programming

By default, the Common Dialog control is not in the Forms toolbox, so you need to add it. To do this, you need to be in a form window with the toolbox window visible. If you do not have a user form added, select Insert User Form from the code menu. You should then see the form and a toolbox window. If the toolbox window is not visible, click the form area to bring focus to the window.

Select Tools Additional Controls from the VBE menu. This will take a short time while VBA searches your computer for all available controls. Your screen should now look similar to Figure 10-1. This screen provides a list of additional controls available and is very comprehensive. The list of available controls will depend on what applications are loaded onto your computer.

Figure 10-1: Adding the Common Dialog control

Scroll down the list until you come to Microsoft Common Dialog control. Check the box and click OK. A Common Dialog control will now be visible in the toolbox. If you cannot see which icon it is, hover your mouse over each icon in turn and read the tooltip text box that appears when you do this. You will eventually come to Common Dialog.

Click the control and drag it onto a user form. The form it goes onto and its position do not matter because it is invisible at runtime. The control is used from VBA code and the form is only a container for it. The form does not even have to be running for the code to work, so it can be referenced from anywhere in your application.

The Open Dialog

The Open dialog allows the user to select a drive, a directory, a filename extension, and a filename, as shown in Figure 10-2.

Figure 10-2: The Open dialog

To display the dialog, set the properties for filters and then set the action property. Type the following code into a module and then run it:

Sub Show_Open() UserForm1.CommonDialog1.CancelError = True On Error GoTo errhandler UserForm1.CommonDialog1.Filter = _ "All files (*.*)*.*Text files (*.txt)" UserForm1.CommonDialog1.Action = 1 MsgBox UserForm1.CommonDialog1.FileName errhandler: Exit Sub End Sub

The CancelError property is first set to True. On a File Open dialog, you get an OK button and a Cancel button. Setting the CancelError property to True produces an error when the Cancel button is pressed. Unfortunately, this is the only way of testing whether the user pressed the Cancel button or the OK button; it is not an elegant way to do this.

This brings you to the next line of code that sets up what to do if there is an error. Code execution jumps to a code label called errhandler later on in the code. A code label is inserted by using a unique name and inserting after it. This sets up a reference point in your code that you can branch to using GoTo .

The next line sets up the filter for file type. In this example, there is a filter for all files and for text files only. This dictates what appears in the File Type combo box. The vertical bar used to separate is obtained by using shift and \. The example gives two filters ‚ one for all files (*.*) and one for text files (*.txt). If you wanted only Word or Excel files, you could use the filters *.doc or *.xls, respectively.

Finally, the Action property is set to 1, which means display the Open File dialog. The user then makes a selection and clicks OK. The message box then displays the filename selected. If the user clicks Cancel, an error is created and the code execution jumps to errhandler and exits.

You can alter the caption in the title bar by setting the DialogTitle property:

UserForm1.CommonDialog1.DialogTitle = "Open"

If you do not want the Read-Only check box, then you can set the Flags property:

UserForm1.CommonDialog1.Flags = 4

You can set more than one parameter in the Flags property by using the Or statement, as you saw earlier in Chapter 6 (for example, 8 Or 4 ). All the properties you wish to apply to the dialog must be set before the Action property is set or they will have no effect.

You can also use the Object Browser (press f2 and search on CommonDialog) to investigate further properties for this control.

The Save As Dialog

The Save As dialog works in almost exactly the same way as the previous Open dialog example, but the Action property is set to 2:

Sub Show_Save() UserForm1.CommonDialog1.CancelError = True On Error GoTo errhandler UserForm1.CommonDialog1.Filter = _ "All files (*.*)*.*Text files (*.txt)" UserForm1.CommonDialog1.Action = 2 MsgBox UserForm1.CommonDialog1.FileName errhandler: Exit Sub End Sub

The Color Dialog

The Color dialog allows the user to select a color from a palette or create and select a custom color, as shown in Figure 10-3.

Figure 10-3: The Color dialog

The code works in the same way as for the Open dialog except that the Action property has a value of 3 and it returns a color value:

Sub Show_Color() UserForm1.CommonDialog1.CancelError = True On Error GoTo errhandler UserForm1.CommonDialog1.Action = 3 MsgBox UserForm1.CommonDialog1.Color errhandler: Exit Sub End Sub

The message box displays the number of the color selected by the user.

The Font Dialog

The Font dialog allows the user to select a font by specifying a typeface, point size , color, and style, as shown in Figure 10-4. Once the user makes selections in this dialog, the following properties contain information about the user's selection:

Figure 10-4: The Font dialog

To select Color, Strikethru, or Underline, you must set the Flags property to &H100& (hexadecimal). You can combine this with other flag values by using the Or operator.

The code to display this works in the same way as the Open dialog, but the Action property is set to 4, and the Flags property needs to be set as follows :

Note you must set the Flags property before displaying the Font dialog or you will get the error message ‚“No Fonts Exist. ‚½

Sub Show_Fonts() UserForm1.CommonDialog1.CancelError = True On Error GoTo errhandler UserForm1.CommonDialog1.Flags = 3 or &H100& UserForm1.CommonDialog1.Action = 4 MsgBox "FontBold is " & UserForm1.CommonDialog1.FontBold MsgBox "FontItalic is " & UserForm1.CommonDialog1.FontItalic MsgBox "FontName is " & UserForm1.CommonDialog1.FontName MsgBox "Font Size is " & UserForm1.CommonDialog1.FontSize MsgBox "Font Color is " & UserForm1.CommonDialog1.Color MsgBox "Font StrikeThru is " & UserForm1.CommonDialog1.FontStrikethru MsgBox "Font Underline is " & UserForm1.CommonDialog1.FontUnderline errhandler: Exit Sub End Sub

Note that the Color property returns the number of the color similar to the Color dialog; FontName and FontSize return actual values. The rest return True or False based on the selection in the dialog.

The Print Dialog

The Print dialog allows the user to specify how output should be printed. The user can specify a range to be printed and the number of copies, as shown in Figure 10-5. This dialog also allows the user to select a printer and allows the user to configure that printer. Bear in mind any changes made to a printer configuration apply to all applications.

Figure 10-5: The Print dialog

This dialog does not print to a printer ‚ you have to write your own code to do that. An easier method might be to use the dialogs built into Excel for printing; see the next section for more information on how to do that.

When the user makes a selection at runtime, the following properties hold information about that selection:

In Excel, the Pages option is unavailable.

The code to display this works in the same way as for the Open dialog, but the Action property is set to 5:

Sub Show_PrintDialog() UserForm1.CommonDialog1.CancelError = True On Error GoTo errhandler UserForm1.CommonDialog1.Action = 5 MsgBox "Copies = " & UserForm1.CommonDialog1.Copies MsgBox "Orientation = " & UserForm1.CommonDialog1.Orientation errhandler: Exit Sub End Sub