Chapter 9: Dialogs

Overview

In Chapter 7, we discussed how to use the message box, which is in effect a simple dialog box. They are easy to use from within your code and offer a simple way to communicate to the user. They can halt execution of your code while something is drawn to the user's attention and allow the user to make choices where necessary.

However, what if you want to do something more complicated, such as request that the user supply parameters or select a worksheet? This is done with a UserForm, which allows you to design your own custom user interface, using standard windows controls, so that you end up with a form that has the look and feel of a standard Windows form.

To use a UserForm, you must first insert one into your project. Select Insert UserForm from the code window menu to insert a blank UserForm into your project. Your screen should look like Figure 9-1.

Figure 9-1: Preparing a UserForm

The form is a blank canvas on which you can place controls. Notice that a toolbox window has also been opened; this allows you to select controls to place on the form such as text boxes, check boxes, and so on.

You can also see that the Properties window reflects the properties for the form, of which there are many. (If the Properties window is not open , select View Properties Window from the VBE menu or press F4 .) The UserForm is similar to a Visual Basic form and has similar properties, events, and methods . The UserForm is an object and has a place in the VBA Project tree.

These are the main properties for the form that you need to consider:

Property	Description
Name	References the form in your code. You should change it to something more meaningful than UserForm1.
BackColor	Dictates the background color of your form. Standard gray is quite useful because it follows the general scheme of things, but you may wish to use a different color scheme for certain forms. Click the gray box and hexadecimal number, and a drop-down arrow appears. Click the drop-down and then click the palette in the window that appears underneath, as shown in Figure 9-2.
BorderColor	Same as for BackColor , but sets the color for the border.
BorderStyle	Sets whether you have a border for your form or not.
Caption	Sets the title of the form as it appears in the title bar at the top of the form.
Enabled	Normally set to True so users can interact with the form controls. If you set it to False, everything is grayed out (disabled) and the form cannot be used. This may be useful when a procedure takes time to process and you don't want the user doing other things on your form while this is happening.
Font	Changes the Font characteristics on the form object, as shown in Figure 9-3.Bear in mind that this only sets the font on text printed on the form object itself ‚ it does not affect objects placed on the form such as text boxes and list boxes, although they have similar properties that can be set separately.
ForeColor	Operates in the same way as the BackColor property and sets the color of any text that you print on the form ‚ it does not affect objects placed on the form such as text boxes and list boxes, although they have similar properties that can be set separately.
Height	Can be set by clicking and holding the sizing handles on the edge of the form and dragging them to the size that you want.
Left	Indicates the distance from the left-hand side of the screen container (Excel) where the form will appear.
Enabled	Determines whether the control is available for user input. If not enabled it is grayed out. Value is True or False.
Picture	You can add a picture to the form as a background. Click the word (None) and a box with an ellipsis (...) will appear to the right. Click that box and you will be taken to a file selection dialog. Select your graphic file and click OK. To remove a picture, delete the contents of the property and it will be replaced with (None).
PictureAlignment	Gives you a drop-down that allows you to set where the picture will be aligned in relation to the form, for example, top left, bottom right, and so on.
PictureSizeMode	Gives you a drop-down with options for how the picture is to be displayed. 0 ‚ Mode Clip ‚   ‚   The default. The picture is displayed as normal size and cropped to fit onto the form. 1 ‚ Mode Stretch ‚   ‚  The picture is stretched both horizontally and vertically to fit onto the form. This can lead to a distorted image. 3 ‚ Mode Zoom ‚   ‚  The picture is enlarged to fit the form without distorting the image.
Picture Tiling	If you are using a small picture, you can opt to tile it across the form by setting this property to True. This can be used for a company logo.
Width	This can be set by clicking and holding the sizing handles on the edges of the form and then dragging them to the size that you want.

Figure 9-2: The color palette for the BackColor property

Figure 9-3: The Font property