Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
When you cancel a generic VBA input box it simply returns an empty string. This works a little differently for an Excel input box:
We almost fell into the trap! But what happens when we click the Cancel button?
You don't need any files for this section.
Click here to download a workbook containing the sample code.
Create a new workbook and insert a module in the VBE.
To demonstrate what happens when an Excel input box is cancelled, we'll return the result to a Variant variable. Create a new subroutine called CancelInputBox and add code to return the result of an Excel input box to a Variant variable:
It doesn't matter what question you display on the input box as we're going to cancel it anyway.
We can step through the code above and then cancel the input box when it appears:
It doesn't matter what you type in when you're going to cancel the input box.
Using the Locals window reveals the value that is returned by the input box:
The input box returns the Boolean value False when it is cancelled.
The default value that is returned when an Excel input box is cancelled is the Boolean value False. The exact value that you'll end up with in the variable depends on the data type that you're using. The table below shows a brief summary of these values:
Variable data type | Value returned |
---|---|
Boolean | False |
String | "False" |
Any numeric data type | 0 |
Date | #00:00:00# |
To practise cancelling an Excel input box:
Sub CancelMe()
Dim YourName As String
YourName = Application.InputBox("Enter your name")
MsgBox "Hello " & YourName
End Sub
It doesn't matter if you type anything into the input box, just click the Cancel button.
Not the most useful message!
Sub CancelMe()
Dim YourName As String
YourName = Application.InputBox("Enter your name")
If YourName = "False" Then
MsgBox "Subroutine cancelled", vbExclamation
Exit Sub
End If
MsgBox "Hello " & YourName
End Sub
A slightly more sensible message.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.