VBA - Inputbox - I want to Exit Sub on Cancel?

Question

I have a series of msgbox and inputbox commands to gather information and place into excel. Certain answers bring the user down a certain path

An InputBox give a choice of "vbOK" and "vbCancel".
If OK is chosen it takes the entered text by default

Is there a way to control the vbCancel when that is chosen?
I think my problem is that I need to dimension my variable in accordance with the selection.

Here is a simplified section of code.
Dim OfficeFax as String

OfficeFax = inputbox("What is the fax number at " & job, "Fax Number", "Enter work fax number here or leave blank")

If OfficeFax = "Enter work fax number here or leave blank" Or OfficeFax = "" Then

OfficeFax = "Not Available"

End If

Range("d15") = OfficeFax


I want to include something like

If OfficeFax = vbCancel Then
Exit Sub
End If

Answer 1

The cancel returns a null value but I see you are accepting null values so checking for the null value is out of the question.



And as far as I know there is NO WAY to detect the Cancel button on the InputBox besides checking for the null value.



So the best sollution would be to create your own custom inputbox using by adding a form in your workbook.







Edit:

Hey very nice found !, I did not knew that :)



Greets Automicss.

Answer 2

Vba Exit Sub

Answer 3

If the user hits the CANCEL button, your input box FUNCTION will return a NULL STRING (intrinsic constant vbNullString, or "" - with nothing between them). You can check for either of those and it will help you detect the CANCEL click. an example: Private Sub Login() Dim strUserName as String strUserName = inputBox("Please Enter Your Username") If strUserName = "" Then 'do something with the missing value Else 'do something with the value given End If End Sub or you can do the following Private Sub Login() Dim strUserName as String strUserName = inputBox("Please Enter Your Username") If strUserName = vbNullString Then 'do something with the missing value Else 'do something with the value given End If End Sub I have showed VB-6 code, which is the VBA code for everything until Office 2007. The code may be different if you are using Office 2007, which uses Visual BASIC.NET as the VBA foundation.

Answer 4

This Site Might Help You.



RE:

VBA - Inputbox - I want to Exit Sub on Cancel?

I have a series of msgbox and inputbox commands to gather information and place into excel. Certain answers bring the user down a certain path



An InputBox give a choice of "vbOK" and "vbCancel".

If OK is chosen it takes the entered text by default



Is there a way to...