Chapter 7: Forms (Microsoft Forms Library)

Forms are free-standing windows in which various items can be placed. Excel recognizes countless predefined forms, such as forms for file selection or for setting options. Additionally, you can create forms or set up worksheets to act like forms with an entire palette of controls.

7.1 Predefined Dialogs

Standard Excel Dialogs

Excel is equipped with a huge number of predefined dialog objects. These dialogs are used by Excel for its everyday tasks , such as for selecting a file name , input of a search text, setting options of PASTE SPECIAL. The dialogs appear automatically as soon as the corresponding menu or keyboard command is executed.

In a VBA program you can select these forms with the Application method Dialogs and use Show to display and execute them. With the command given below a dialog box appears for the arrangement of windows. You can now input the desired state of the windows (overlapping, split, and so on). As soon as you click on Ok, the windows will indeed be positioned as you indicated. As a programmer you need not concern yourself with the evaluation of the dialog box.

 Application.Dialogs(xlDialogArrangeAll).Show 

The execution of the Show method is possible only when a suitable sheet is active or a suitable object has been selected. Thus, for example, it is not possible to display the dialog for setting the border of a cell ( xlDialogBorder ) while a chart sheet is active.

The method Show can be used both as a command and as a function. In the latter case it returns True if the dialog was exited normally with Ok, and False if the dialog was ended with Cancel, Esc, or the window's close box.

 result = Application.Dialogs(xlDialogArrangeAll).Show 

Predefined standard dialogs cannot be altered . If you have need of a dialog box that is similar to one that already exists, you will have to define it from scratch. User -defined forms are handled in the next section.

Passing Parameters to a Dialog

A list of all xlDialog constants can be found in the object browser (library: Excel; object: xlBuiltinDialogs ). Up to thirty parameters can be passed to the method Show , with which settings can be set in the dialog box. A description of the parameters can be found in the on-line help under the link "Built-In Dialog Box Argument Lists." But even so, the passing of parameters remains a tedious and difficult undertaking. This will become clear from the following example: The dialog box for opening an Excel file ( xlDialogOpen ) is described as follows in the on-line help:

xlDialogOpen

file_text, update_links, read_only, format, prot_pwd,
write_res_pwd, ignore_rorec, file_origin, custom_delimit,
add_logical, editable, file_access, notify_logical, converter

That this is a dialog for opening a file (and not, say, a window or a workbook) you can figure out. But more difficult is figuring out what the parameters mean, unless you can immediately understand what values add_logical expects and what effect it has.

Let us proceed now to parameter passing: Like all other VBA methods , Show uses the mechanism of named parameters. However, since Show must deal with many different dialogs, the parameter names are rather simple: Arg1 , Arg2 , Arg3 , etc. If you wish in opening a file to activate the option button Open Read-Only, the corresponding instruction looks as follows:

 result = Application.Dialogs(xlDialogOpen).Show(Arg3:=True) 

Thus you must count to obtain the number of the parameter that you need. It is clear that the program code will not be very self-explanatory.

Simulating Keyboard Input in Dialogs

Show displays a dialog box, but the input of parameters is left to the user of your program. It can often be useful to simulate keyboard input. For this you have available the Application method SendKeys . Note that in using this method it must be executed before the dialog box is displayed, which seems unlogical. The reason is that Windows saved the simulated key sequence in a keyboard buffer and executes the keyboard input only when it has the chance ”after the appearance of the dialog box.

The following example shows once again the dialog box for arranging windows (Figure 7-1), but it has selected the option "Horizontal" with Alt+O. The user needs only to confirm the dialog with Return.

Figure 7-1: Dialog box for arranging windows

 SendKeys "%o" Application.Dialogs(xlDialogArrangeAll).Show 

The syntax of the character string in which the simulated keyboard input is given in SendKeys is described extensively in the on-line help for this method. In principle, it is also possible with SendKeys to terminate the input in the dialog at once with Ok (that is, by simulating Return). In this way you can execute various Excel commands directly via the detour of a dialog box. If the property ScreenUpdating is set to False , then the user of your program doesn't even see the dialog box. Nonetheless, this method is not to be recommended, for the following three reasons:

• By executing the appropriate methods the same result can be achieved usually more simply and always significantly faster. (However, it is not always completely simple to find the right method. This problem has no doubt already made itself known to you. In the case of window arrangement the suitable method is Arrange , and it is used by the Windows enumeration.)

• The necessary keyboard inputs are naturally dependent on the regional version of Excel. That is, their code in not usable internationally.

• If Microsoft changes the construction of individual dialogs in a future version of Excel, your program will most likely no longer operate correctly, if at all. (The simulation of keyboard input is indeed based on the fact that individual elements of the dialog box can be accessed with Alt+Key.)

Dialogs for File Selection

You can execute the dialog for file selection via Dialogs with the constant xlDialogOpen or xlDialogSaveAs (see Figure 7-2). Then the operation (opening or saving) will be executed at once.

Figure 7-2: Save As dialog (GetSaveAsFilename)

Instead of this you can also use the two methods GetOpenFilename and GetSaveAsFilename . Then the file selection dialog box will be displayed, but only the selected file name will be returned (without opening or saving a file). Therefore, you have more flexibility with these methods with regard to further response.

 filename = Application.GetSaveAsFilename 

The Database Form

The dialog for selecting, changing, and input of data records is not invoked with Dialogs( ).Show , but with ShowDataForm . The use of the database mask is described in Chapter 11.

Since Excel 5 it has no longer been possible to change the database form. If you wish to set up your own database form, you will have to program all the elements of the database form yourself. However, this requires considerable effort.

Warnings

Many unwanted forms can appear during the execution of methods. Usually, these are in the form of warnings that advise against the consequences of the operation (such as loss of data). For example, with the instruction Sheets( ).Delete for the deletion of a sheet of a workbook there appears an alert asking whether you really (cannot be undone) want to delete it.

While in the normal use of Excel such warnings are quite practical, they are a nuisance in the running of a program, since they interrupt execution and confront the user of the program with a cryptic warning. To get around this problem you can set the Application property DisplayAlerts in such a way that no warnings at all are displayed.

The Functions MsgBox and InputBox

The two functions MsgBox and InputBox have already been briefly described in conjunction with character strings (Chapter 5). MsgBox displays a text in a small window that must be acknowledged with Ok. With a suitable setting of the second parameter it is also possible to have several buttons displayed (such as Yes, No, Cancel) and the selection evaluated. See Figures 7-3 and 7-4. InputBox enables the input of a simple character string that is returned by the function.

Figure 7-3: VBA input box

Figure 7-4: Message box

 result = Inputbox("Please type in your name:") result = MsgBox("Do you really want to delete this file?", _   vbYesNo + vbQuestion) If result = vbYes Then ... 

The Method Application.InputBox

Although the function InputBox described above is available from the VBA library (and thus can be used in the same form in Word, Acces, etc.), the Excel library offers a second, like-named, variant that is realized as a method of the Application object. In contrast to the traditional InputBox , the function Application.InputBox can also be used for input of formulas and ranges of cells . See Figure 7-5. Furthermore, the position at which the input window appears can be determined in advance.

Figure 7-5: Excel input box

Decisive for the application of Application.InputBox is the last parameter, by which the type of input can be set, as long as no text input is to be given. The most important values for this parameter are 0 (formula), 1 (number), 8 (range), 64 (matrix of cells).

If type 1 (number) is given in Application.InputBox , then the user is also permitted to input a formula in the form "= 2 + 3." The formula will be automatically evaluated, and InputBox returns the value 5. With Type:=8 the user can select a range of cells with the mouse. The user can even select another window or another sheet ”actions that are not permitted in normal Windows dialog boxes.

In calling this function you give the first parameter in the usual way, and the eighth by name:

 Dim b As Range Set b = Application.InputBox("Please input a range of cells", _   Type:=8) 

InputBox returns the affected selection in the format determined by the Type parameter. If the user ends input by pressing Cancel, then the method returns the Boolean value False . This situation makes the evaluation of the input for Type:=8 somewhat complicated: Since the result is normally a Range object, the assignment of the result must take place via Set . But this leads to an error if InputBox returns only the Boolean value False instead of a Range object. Therefore, properly written code would look like this:

 Dim b As Range On Error Resume Next Set b = Application.InputBox("Please input a range of cells", _   Type:=8) If Err > 0 Then 'an error occurs   MsgBox "That was not a range of cells" End If 

In the case of type 64 (matrix) InputBox returns a Variant field with the values of the given range (for example, a 3 — 2 field for the range A1:B3).

Pointer  

A rather advanced example of the use of InputBox can be found in Chapter 11 under the heading "editing a preexisting invoice." There the command is used to select a row of a worksheet. The user can click on a cell with the mouse, and the program takes takes the row number of the formula returned by InputBox.

Positioning of Dialog Boxes

Forms that are not invoked from a pop-up menu but via the keyboard or the main menu usually appear precisely where they are the least useful. If you execute the command WindowArrange and place the mouse for a while in the upper righthand corner of the screen, it could happen that on its own the dialog box appears in the lower left-hand corner of the screen. This is particularly irritating when you are working with a large monitor and have to move a long distance with the mouse to complete the input.

If you move the window of the dialog box, Excel indeed takes note of the new position. But at a later point in time this can be as unsatisfactory as the previous position. Furthermore, apparently it is not the last position of each dialog that is stored individually, but for all dialog boxes together.

In many cases it would be best if the dialog box would appear where the mouse is located. (This is precisely the case with all dialog boxes that are invoked by context menus .) But in the current version there is no opportunity for the programmer to influence the position at which the box appears in the case of predefined dialogs. An exception is InputBox , where the fourth and fifth parameters ( Left and Top ) control the position of the box. With a bit of programming effort you can use these parameters to ensure that the dialog box does not appear over the currently selected cells. (Their position on the screen can be determined with the Range properties Left and Top .)