Out of all the challenges you might face in MS Excel VBA, there is one that may very well be the universal “Achilles Heel.”  The user…  To illustrate this, consider the following scenario: 

You just spent several days building and successfully testing a great MS Excel VBA solution which utilizes the InputBox Function.

Confident that it is ready, you release this new solution to your users fully prepared to receive the accolades that are soon to come to you for a job well done only to find, to your horror, that your perfectly planned solution is totally dependent on the user doing what they are “SUPPOSED” to be doing for things to result in success. 

The fact of the matter is that if there is an element of user involvement, then you must be prepared for their shenanigans! 

This article discusses how you can design your MS Excel VBA project for success by properly handling the InputBox Function’s Cancel Button as well as the user clicking the “OK” Button while leaving the input field blank!

So, let us explore those contingencies, shall we?

What if the User Clicks Cancel?

NOTE: The Cancel Button and the “X” button on the top right of the user prompt window should both be considered the same!

The easiest and most effective way that I have found to handle the siutation where a user clicks the InputBox Function’s Cancel button is to use the StrPtr Function to identify when the Cancel button has been clicked.  If the StrPtr Function returns 0, then you know that the user has clicked the Cancel Button and you can code for that accordingly.

Here is the code so you can copy and paste.  Remember, the “Call” will not work if the “ContingencyTime” Sub is not located in a module named “Contingencies”:

Sub ContingencyTime()
 Dim UserInput As Variant
 UserInput = InputBox(“What city are you in?”, “Input Your City…”, “Raleigh”)
 If StrPtr(UserInput) = 0 Then
 	MsgBox (“Nice Try!!!”)
	Call Contingencies.ContingencyTime
 End If
End Sub

This code first asks the user to input their city with “Raleigh” being prefilled in the input field:

In the event of the user clicking cancel or the “x” button on the top right of the user prompt window, a message box will immediately pop-up saying, “Nice Try!!!”:

After the message box, the user is then taken back to the original user prompt asking them to input their city:

As you can see, this contingency makes clicking “Cancel” no longer an OPTION for the user!

What About Blank Input?

NOTE: This contingency is useful if you need your user to input a value instead of clicking “OK” while leaving the input field blank.

The easiest and most effective way that I have found to handle the shenanigan where a user tries to click “OK” even though they have left the input field blank is to use vbNullString.  If your variable equals vbNullString, then you know that the user has clicked “OK” while leaving the input field blank and you can code for that accordingly.

Here is the code so you can copy and paste.  Remember, the “Call” will not work if the “ContingencyTime” Sub is not located in a module named “Contingencies”:

Sub ContingencyTime()
 Dim UserInput As Variant
 UserInput = InputBox(“What city are you in?”, “Input Your City…”, “Raleigh”)
 If UserInput = vbNullString Then
 	MsgBox (“You Cannot Leave the Input Field Blank!!!”)
	Call Contingencies.ContingencyTime
 End If
End Sub

This code first asks the user to input their city with “Raleigh” being prefilled in the input field:

In the event of the user clicking the “OK” Button while leaving the input field blank a message box will immediately pop-up saying, “You Cannot Leave the Input Field Blank!!!”:

After the message box, the user is then taken back to the original user prompt asking them to input their city:

As you can see, this contingency makes clicking the “OK” button while leaving the input field blank no longer an OPTION for the user!

Protect Your Solution by Combining Both Contingencies

NOTE: Putting these two contingencies together should allow you to feel confident that your use of the InputBox function will not be a cause of heartache for you in the future.

A nice and clean way to put these two contingencies in place would be to use ElseIf and Else.

Here is the code so you can copy and paste.  Remember, the “Call” will not work if the “ContingencyTime” Sub is not located in a module named “Contingencies”:

Sub ContingencyTime()
 Dim UserInput As Variant
UserInput = InputBox(“What city are you in?”, “Input Your City…”, “Raleigh”)
 If StrPtr(UserInput) = 0 Then
 	MsgBox (“Nice Try!!!”)
	Call Contingencies.ContingencyTime
 ElseIf UserInput = vbNullString Then
 	MsgBox (“You Cannot Leave the Input Field Blank!!!”)
	Call Contingencies.ContingencyTime
 Else
 	Call Strategies.ImplementStrategy(UserInput)
 End If
End Sub

This code first asks the user to input their city with “Raleigh” being prefilled in the input field:

In the event of the user clicking cancel or the “x” button on the top right of the user prompt window a message box will immediately pop-up saying, “Nice Try!!!”:

After the message box, the user is then taken back to the original user prompt asking them to input their city:

Now the pesky user tries to click the “OK” button while leaving the input field blank and they are met with our message box of justice:

Which then leads them back to the original user prompt asking them to input their city:

And just like that, we beat the user’s shenanigans with our contingencies!

As you could see in that final example, the solution was able to ensure that some form of input from the user would be passed to the “ImplementStrategy” Sub located in the “Strategies” module via the “UserInput” variable. 

Additionally, the “UserInput” variable can be qualified even further before it is passed on and you can feel free to experiment with and tweak the provided code to account for whatever qualifications your solution might need. 

No matter how you decide to implement these contingencies into your MS Excel VBA project you can at least know that having them in place will make your solution much better prepared to handle the user’s potentially nonsensical use of the VBA InputBox function’s user prompt and that alone is worth a proverbial “fist bump”!