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.

Code 1: Coding the Cancel button on an message box - MsgBox function.

Sub MBCancel()
'' Message box Cancel demonstration
Dim MBButton As Integer
Dim MBPrompt As String
    MBPrompt = "PRESS: " & vbNewLine & "YES to continue; NO to Restart, or CANCEL to Exit"
    '' more code goes here
    MBButton = MsgBox(MBPrompt, vbYesNoCancel, "xlfStar APP")
    If MBButton = vbCancel Then
        Debug.Print "xlfSTAR ============================"
        Debug.Print "MBButton value: " & MBButton
        Debug.Print "vbCancel value: " & vbCancel
        Debug.Print "MB Cancel button pressed at " & Time & vbNewLine
        Exit Sub
    End If
    '' more code goes here
End Sub

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.

Code 2: InputBox method - DisplayAlert coding

Sub IBblankOK()
'' InputBox method Cancel type 0 - a Formula
Dim IBdata As String
Dim Ans As Double
Dim IBPrompt As String
    IBPrompt = "Enter value " & vbNewLine & "Press OK to continue; or CANCEL to Exit"
    '' more code goes here
    Application.DisplayAlerts = False                           '' suppress WS error alerts
    IBdata = Application.InputBox(Prompt:=IBPrompt, _
                                  Title:="xlfStar APP", _
                                  Type:=1)
    If Left(IBdata, 1) <> "=" Then
       If IBdata = False Then
            Debug.Print "starXLF ============================"
            Debug.Print "IB Cancel button pressed at " & Time & vbNewLine
            Exit Sub
        End If
    End If
    Ans = Evaluate(IBdata)
    '' more code goes here
    Application.DisplayAlerts = True                            '' restore WS error alerts
End Sub

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.

Code 2 (a): InputBox method - type 0 - Cancel button coding

Sub IBCancel_0()
'' InputBox method Cancel type 0 - a Formula
Dim IBdata As String
Dim Ans 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:="=LN(1.2 / 1.1)", _
                                  Type:=0)
    If Left(IBdata, 1) <> "=" Then
       If IBdata = False Then
            Debug.Print "starXLF ============================"
            Debug.Print "IB Cancel button pressed at " & Time & vbNewLine
            Exit Sub
        End If
    End If
    Ans = Evaluate(IBdata)
    '' more code goes here
End Sub

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, _
                                  Type:=1)
    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.

Code 3 (b): InputBox method - type 1 - Cancel button coding

Sub IBCancel_1b()
'' InputBox method Cancel type 1 - a Number
Dim IBdata As Variant
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, _
                                  Type:=1)
    If IBdata = False And TypeName(IBdata) = "Boolean" Then
        Debug.Print "starXLF ============================"
        Debug.Print "IB Cancel button pressed at " & Time & vbNewLine
        Exit Sub
    End If
    '' more code goes here
End Sub

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.

Code 4: InputBox method - type 2 - Cancel button coding

Sub IBCancel_2()
'' InputBox method Cancel type 2 - Text (a String)
Dim IBdata As Variant
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:="ANZ.AX", _
                                  Type:=2)
    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

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.

Code 4: InputBox method - type 2 - no explicit Cancel button coding

Sub IBCancel_2()
'' InputBox method Cancel type 2 - Text (a String)
Dim IBdata As String
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:="ANZ.AX", _
                                  Type:=2)
    '' Pressing CANCEL returns "Cancel" to the IBdata variable
    '' more code goes here
End Sub

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.

Code 5: InputBox method - type 4 - Cancel button coding

Sub IBCancel_4()
'' InputBox method Cancel type 4 - a Logical value (True or False)
Dim IBdata As Boolean
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:=True, _
                                  Type:=4)
    If IBdata = False Then
        Debug.Print "starXLF ============================"
        Debug.Print "IB returned FALSE at " & Time
        Debug.Print "Note: it is not possible to distinguish between an"
        Debug.Print "entered False and the Cancel return value of False" & vbNewLine
        '' Exit Sub
    End If
    '' more code goes here
End Sub

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.

Code 6: InputBox method - type 8 - Cancel button coding

Sub IBCancel_8()
'' InputBox method Cancel type 8 - a Cell Reference (as a Range object)
Dim IBdata As Range
Dim IBPrompt As String
    IBPrompt = "Enter range " & vbNewLine & "Press OK to continue; or CANCEL to Exit"
    '' more code goes here
    On Error Resume Next
    Set IBdata = Application.InputBox(Prompt:=IBPrompt, _
                                      Title:="xlfStar APP", _
                                      Default:="$A$1:$B$4", _
                                      Type:=8)
    If IBdata Is Nothing Then
        Debug.Print "starXLF ============================"
        Debug.Print "IB Cancel button pressed at " & Time & vbNewLine
        Exit Sub
    End If
    '' more code goes here
End Sub

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.

Code 7: InputBox method - type 16 - Cancel button coding

Sub IBCancel_16()
'' InputBox method Cancel type 16 - An Error value
Dim IBdata As Variant
Dim IBPrompt As String
Dim tmp
    IBPrompt = "Enter ERROR value: " & vbNewLine & "Press OK to continue; or CANCEL to Exit"
    '' more code goes here
    '' Error code: #N/A = 2042
    IBdata = Application.InputBox(Prompt:=IBPrompt, _
                                  Title:="xlfStar APP", _
                                  Default:="#N/A", _
                                  Type:=16)
    If Not IsError(IBdata) Then
        If IBdata = False Then
            Debug.Print "xlfSTAR ============================"
            Debug.Print "IB Cancel button pressed at " & Time & vbNewLine
            Exit Sub
        End If
    End If
    '' more code goes here
End Sub

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}", _
                                  Type:=64)
    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.

This example was developed in Excel 2013 64 bit

VBA code link txt 8KB
Revised: Friday 24th of February 2023 - 02:37 PM, Pacific Time (PT)