Lead

Sep 16 06 3:02 AM

Tags : :

The InputBox function is frequently used for the input of data. Sometimes (e.g. in a loop) it may be desirable to allow a user to enter nothing and proceed further, but to (say) exit the loop if the user clicks Cancel.

With the InputBox function, if the user clicks Cancel, the function returns a zero-length string, so the InputBox function can't differentiate between clicking Cancel and a users entry of "" (no entry) in the input box. This means we can't use it to fully validate the data being input by the user.

You can test the truth of this statement with the procedure below, run the code and click Cancel, then run the code again and click OK without making an entry. In both cases you'll get a message box saying "Cancel was pressed!".

CODE
Option Explicit

Sub CheckIfCancelWasPressed1()
     'the inputbox function
     Dim Response As String
     Response = InputBox("Your string here:")
     If Response = "" Then MsgBox "Cancel was pressed!"
End Sub


To overcome this chortcoming of the InputBox function we need to use the InputBox method instead. The InputBox method was specifically designed to allow validation of user data, and, in the example given below you'll see that Visual Basic can differentiate between the 'False' generated by clicking the Cancel button and a 'False' typed into the InputBox by the user (but note this is not true if you just use Type:=2 instead of 1 + 2)
CODE
Option Explicit

Sub CheckIfCancelWasPressed2()
     'the inputbox method
     Dim Response As String
     Response = Application.InputBox("Your string here:", , , , , , , 1 + 2)
     If Response = "False" Then MsgBox "Cancel was pressed!"
End Sub

If I irradiate my cat will that give it eighteen half-lives?

The major part of getting the right answer lies in asking the right question...