box Cancel button coding

At first glance, the Cancel button on an Excel message box, and the Cancel button on an Excel input box would seem to have much in common, but the coding required is unique to each object.

1. Coding the Excel message box cancel button

The message box MsgBox function provides customization of the buttons by allowing selection from a predefined set of 6 items, each identified by a constant (value): vbOKOnly (0), vbOKCancel (1), vbAbortRetryIgnore (2), vbYesNoCancel (3), vbYesNo (4), and vbRetryCancel (5).

Return values are also specified by constants, with the values shown in brackets: vbOK (1), vbCancel (2), vbAbort (3), vbRetry (4), vbIgnore (5), vbYes (6), and vbNo (7). Suppose that the user has specified vbYesNoCancel as the message box button combination, when the user clicks the Cancel button, the integer 2 is returned, and this value is represented the constant vbCancel. Note that the MsgBox Cancel button returned vbCancel.

In the example shown in code 1, the Cancel button click event means that the value of vbCancel is assigned to the MBButton variable (line 7). The the logical test returns MBButton=vbCancel=True in code line 8. Pressing the ESC key has the same effect as clicking Cancel.

2. Coding the Excel input box method cancel button

In contrast, the Excel InputBox Application.InputBox method offers no facility for customization of the box buttons. There is only an OK button and a Cancel button. The InputBox method has an optional Type parameter that specifies the return data type. There are 8 different values that can be passed in the Type parameter: 1 (a Formula), 2 (a Number), 4 (a Logical value - true or false), 8 (a Cell reference as a Range object), 16 (an Error value), and 64 (an Array of values). Each of the different Types will be demonstrated in the following code windows 2 to 9.

According to the syntax for the InputBox method, the Cancel button returns False when clicked.

xlf Note: [OK] with blank input

Pressing the OK button with blank input returns an alert message (figure 1), in the case of type 0 (formula) and 1 (number). A type 2 (text) is the default and thus able to handle the "" from the blank.

WS prompt alert
Fig 1: WS alert - as shown by the Exclamation icon. Use the DisplayAlert property to suppress this message

Use the Application DisplayAlerts property to suppress the WS alert, see code 2 line 8.

2.1 xlf Type 0

The example in code 2 (a), allows the user to enter a formula as shown by the Type 0 parameter. The Cancel button code is in line 13.

If a valid formula is entered, however, such as the default parameter with the log transformation in line 10, then the code in line 13 will fail due to a type mismatch. The IBdata variable is type text, whereas False is of the type Boolean. To solve this issue, the code in line 13 is only executed if the content of IBdata is not a formula. The existence of a formula is determined in line 12, by checking if the first character in IBdata is an equals sign, because a formula always starts with =.

To calculate the return value for the formula, we use the Application Evaluate method in line 19. Evaluate("=LN(1.2 / 1.1)") returns 8.70113769896297E-02 to the Ans variable.

2.2 xlf Type 1

In business spreadsheets, the Application.InputBox method is most often used with a number, text, or range type parameter. These section are shown by the border marker on the left "   ". Code 3 (a) has a type 1 allowing entry of a number. The IBdata variable is of the type double, and the cancel button code is in line 11. In this case, the number value in IBdata, and the value 0 for False will not cause a Type mismatch. When Cancel is pressed, IBdata contains the default value 0, and 0=False=True. A problem occur if the User enters the value 0. This would cause a incorrect return value of False. The correction for a 0 entry is shown in code 3 (b).

Code 3 (a): InputBox method - type 1 - Cancel button coding
Sub IBCancel_1a()
'' InputBox method Cancel type 1 - a Number
Dim IBdata As Double
Dim IBPrompt As String
    IBPrompt = "Enter value " & vbNewLine & "Press OK to continue; or CANCEL to Exit"
    '' more code goes here
    IBdata = Application.InputBox(Prompt:=IBPrompt, _
                                  Title:="xlfStar APP", _
                                  Default:=123.45, _
    If IBdata = False Then
        Debug.Print "starXLF ============================"
            Debug.Print "IB Cancel button pressed at " & Time & vbNewLine
        Exit Sub
    End If
    '' more code goes here
End Sub											

To allow for the 0 value, we change the IBdata type to Variant in code 3 (b) line 3. If a 0 is entered, then IBdata will be a Variant/Double type with value 0. If Cancel is pressed, then IBdata will be a Variant/Boolean type with value False (equal to zero). Thus line 11 includes: If IBdata = False And TypeName(IBdata) = "Boolean" Then to handle the Cancel button for a parameter type 1.

2.3 xlf Type 2

The Cancel button for a parameter type 2 would appear to be straight forward, however, IBdata must be of the Variant (line 3), otherwise pressing the OK button and passing a text string such as the default value will cause a Type Mismatch error at line 11.

If Cancel is pressed then IBdata contains False as a Boolean, then False=False=True in line 11 of code 4.

Explicit coding of the Cancel button is only essential with a type 8 Range Object () - see section xlf Type 8. Code 4 is now revised to skip the Cancel button coding section.

2.4 xlf Type 4

A type 4 provides for a Logical - True/False input, therefore, IBdata is of the type Boolean. Coding the Cancel button is this case presents a challenge. Basically, there is no way to distinguish between a False entered by the user, and a False returned by the Cancel button as stated in lines 14 and 15 of code 5.

2.5 xlf Type 8

To allow for the selection of a Range object a Type 8 is required. In code 6, IBdata is declared as a Range object (line 3). If a Type 8 is used, then the statement must commence with a Set statement (line 8).

The Range is identified by its address or name and IBdata is the range object. If Cancel is pressed, however, the value False is assigned to the range object. As False is not a valid Range, then the Cancel procedure causes a type mismatch error to occur, with IBdata set to Nothing. In other words, IBdata has not been assigned a range, hence the Nothing value.

Unlike the error in code 2 which occurred after the InputBox statement, in this case the error occurs because of the Inputbox statement. To suppress the error message, the On Error Resume Next statement is added at line 7. Then, if Cancel is pressed, the If IBdata Is Nothing statement is used in place of the False syntax.

2.6 xlf Type 16

A Type 16 allows entry of an error value from the list: #DIV/0, #NAME, #N/A, #NULL, #NUM, #REF, and #VALUE. IBdata is declared as a Variant. To handle the Cancel button we use a two stage process. Firstly, we determine if IBdata is not an Error by using the VBA IsError function (line 13). If a valid error code has not been entered then the False return value for the Cancel button in processed in line 14.

2.7 xlf Type 64

In this final example, the input data is an array as identified by a parameter Type 64. The array can be an array constant or an Excel array from a range. Compared to the range for a Type 8, the Type 64 only copies the values from the range to IBdata, whereas the Type 8 sets IBdata as the range object.

An array constant is entered as the Default value in line 9 of code. This particular array constant is 2 rows by 3 columns. Commas separate the elements in each row, and the semi colon marks the end of the row except for the last row where no row end indicator is required.

To code the Cancel button, we again use a two stage process. If IBdata (of Type Variant - line 3), is a multi element array, then line 12 of code 8 would cause a Type Mismatch error. Therefore, we use the IsArray function preceded by Not. If IBdata is not an array, then the Cancel procedure in line 12 is executed.

Code 8: InputBox method - type 64 - Cancel button coding
Sub IBCancel_64()
'' InputBox method Cancel type 64 - an Array of values
Dim IBdata As Variant
Dim IBPrompt As String
    IBPrompt = "Enter array constant, or select worksheet array: " & vbNewLine & "Press OK to continue; or CANCEL to Exit"
    '' more code goes here
    IBdata = Application.InputBox(Prompt:=IBPrompt, _
                                  Title:="xlfStar APP", _
                                  Default:="{11,12,13;21,22,23}", _
    If Not IsArray(IBdata) Then
        If IBdata = False Then
            Debug.Print "starXLF ============================"
            Debug.Print "IB Cancel button pressed at " & Time & vbNewLine
            Exit Sub
        End If
    End If
    '' more code goes here
End Sub

The Immediate Window is shown in figure 2.

Immediate window
Fig 2: Immediate window - the return values of the Debug.Print statement