Of all the problems you may encounter in MS Excel VBA, one can be a universal Achilles heel. A user… To illustrate this, consider the following scenario:
You have just created and successfully tested an excellent MS Excel VBA solution with the InputBox function for a few days.
Of course it’s ready, present this new solution to your users, who are very ready to receive the praise you will soon receive for a job well done, much to your horror that your perfectly planned solution is entirely dependent on the user doing what he or she has had done to succeed.
The point is that if there is an element of user participation, you have to be ready for the lotteries!
This article describes how to successfully design a VBA MS Excel project by correctly manipulating the Cancel Input Box function key and leaving the input field blank!
So let’s look at those unforeseen circumstances, shall we?
What happens when the user clicks the Cancel button?
NOTE : The Cancel button and the X button in the upper right corner of the user window should be considered equivalent!
The simplest and most effective method I have found for dealing with a situation where a user clicks the Undo button in the input field is to use the StrPtr function to determine when the Undo button is pressed. If the StrPtr 0 function returns, you know that the user has clicked the Cancel button and you can program it accordingly.
Here is the code you can copy and paste Remember that the call will not work if the ContingencyTime subsystem is not in the Contingencies module:
Sub ContingencyTime()
Dim UserInput as option
UserInput = InputBox(In which city are you?, enter your city…, Raleigh)
If StrPtr(UserInput) = 0, then
MsgBox (Nice try!!!)
Call ContingencyTime.contingencyTime
End, as
End Sub
This code first asks the user to enter his city, with Raleigh in the input field:
alt=request for input block data – original width=356 data – original height=150 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/Can-You-Handle-It-VBA-InputBox-Cancel.png data-ez= />
When the user clicks on the Cancel button or the x-button in the upper right corner of the user request window, the message window with the caption appears immediately: Nice try !!!!
alt=MsgBox stating data-orig-width=126 data-orig-height=130 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/1604126542_909_Can-You-Handle-It-VBA-InputBox-Cancel.png />
After the message, the user returns to the original question to enter his city:
alt=request for input block data – original width=356 data – original height=150 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/Can-You-Handle-It-VBA-InputBox-Cancel.png data-ez= />
As you can see, this emergency means that clicking on Cancel is no longer OPTIONAL for the user!
How about an empty entrance?
NOTE : This emergency situation is useful if your user needs to enter a value instead of clicking OK and leaving the input field blank.
The simplest and most effective method I have found to combat fraud when a user tries to click OK, even if he has left the input field blank, is to use vbNullString. If your variable is vbNullString, you know that the user has clicked OK and left the input field empty, and you can encode it accordingly.
Here is the code you can copy and paste Remember that the call will not work if the ContingencyTime subsystem is not in the Contingencies module:
Sub ContingencyTime()
Dim UserInput as option
UserInput = InputBox(In which city are you?, enter your city…, Raleigh)
If UserInput = vbNullString, then
MsgBox (you can’t leave the input field blank!!!!)
Call Contingencies.ContingencyTime
End As
End Substitution
This code first asks the user to enter his city, with Raleigh in the input field:
alt=request for input block data – original width=356 data – original height=150 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/Can-You-Handle-It-VBA-InputBox-Cancel.png data-ez= />
If the user clicks on the OK button and leaves the input field empty, a message window appears immediately: You can’t leave the input field blank!
alt=MsgBox stating data-orig-width=249 data-orig-height=132 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/1604126543_6_Can-You-Handle-It-VBA-InputBox-Cancel.png data-ez= />
After the message, the user returns to the original question to enter his city:
alt=request for input block data – original width=356 data – original height=150 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/Can-You-Handle-It-VBA-InputBox-Cancel.png data-ez= />
As you can see, this emergency means that pressing the OK button, leaving the input field empty, is no longer OPTIONAL for the user!
Protect your solution by combining the two conditional situations
NOTE : The combination of these two possibilities should give you the assurance that using the InputBox will not hurt you in the future.
An excellent and clean way to create these two possibilities would be to use ElseIf and ElseIf.
Here is the code you can copy and paste Remember that the call will not work if the ContingencyTime subsystem is not in the Contingencies module:
Sub ContingencyTime()Dim UserInput in optionUserInput = InputBox(In which city are you? Enter your city…, ReillyElseIf StrPtr(UserInput) = 0, thenMsgBox (Nice try !!!!) Call Contingencies.ContingencyTimeElseIf UserInput = vbNullString ThenMsgBox (You can’t leave the input field empty!))Call ContingencyTimeElseIf UserInput = vbNullString ThenMsgBox (You can’t leave the input field empty!)! Call ContingencyTimeElseCall Strategies.implementStrategy(UserInput)End ifEnd Subs.
This code first asks the user to enter his city, with Raleigh in the input field:
alt=request for input block data – original width=356 data – original height=150 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/Can-You-Handle-It-VBA-InputBox-Cancel.png data-ez= />
When the user presses the Cancel button or the x button, a message window will immediately appear in the upper right corner of the user request window: Nice try !!!!
alt=MsgBox stating data-orig-width=126 data-orig-height=130 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/1604126542_909_Can-You-Handle-It-VBA-InputBox-Cancel.png />
After the message, the user returns to the original question to enter his city:
alt=request for input block data – original width=356 data – original height=150 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/Can-You-Handle-It-VBA-InputBox-Cancel.png data-ez= />
Now the annoying user tries to click the OK button and leaves the input field empty, and he gets our window with the message of justice:
alt=MsgBox stating data-orig-width=249 data-orig-height=132 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/1604126543_6_Can-You-Handle-It-VBA-InputBox-Cancel.png data-ez= />
This will take you back to the original user manual and ask you about your city:
alt= data-orig-width=356 data-orig-height=150 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/Can-You-Handle-It-VBA-InputBox-Cancel.png data-ez= />
And this way we will beat the users with our unforeseen circumstances!
As you can see in this last example, the solution was able to transfer some user information via the UserInput variable to the ImplementStrategy subsystem, which is located in the Strategy module.
In addition, the UserInput variable can be qualified prior to delivery and you are free to experiment with the included code and adapt it to any qualifications required for your solution.
No matter how you decide to implement these eventualities in your VBA MS Excel project, you can at least know that your solution will be much better prepared for the potentially useless use of the VBA InputBox feature by the user, and that alone is worth the famous punch!
Related Tags:
vba inputbox cancel error,vba strptr function,inputbox cancel button,excel vba inputbox yes, no,vba cancel button,excel vba inputbox type,vba if cancel then,excel vba input box on cancel,check inputbox cancel vba,capture excel input box cancel,ms access strptr,vba input box,vba input box with options,vba inputbox yes/no,excel vba input box drop down list,vba inputbox password,excel vba prompt user to select cell,vba data box,strptr,cancel input box vba,inputbox vba,access vba inputbox cancel,application.inputbox cancel,excel vba inputbox cancel stop macro
