Test for InputBox 'Cancel'

dmorse03

Board Regular
Joined
Sep 7, 2002
Messages
59
After an InputBox statement, how do I test to see if user pressed "Cancel" and if so, exit the Sub?
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
If the user clicks Cancel, a zero-length string is returned.

Public Sub inputExample()
TestVal = InputBox("type in value.. or not", "Cancel Test")

If TestVal = "" Then Exit Sub

MsgBox (TestVal)
End Sub
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
Hey Nimrod, what do you think of this variation?

The OP seemed to specify an interest in the Cancel button. In your example, if nothing is entered in the input box, then clicking OK or Cancel would exit the procedure, but you wouldn't know whether OK or Cancel was clicked.

This may be what the OP had in mind but I could be wrong. Do you see any holes in the following method? Thanks...interested in your opinion if this works or can be shortened to differentiate Cancel as to what was actually clicked.

Sub InputBoxExample()
Dim CancelTest As Variant
showInputBox:
CancelTest = Application.InputBox("Enter a value, or click Cancel to exit:")
If CancelTest = False Then
MsgBox "You clicked the Cancel button, Input Box will close.", 64, "Cancel was clicked."
Exit Sub
ElseIf CancelTest = "" Then
MsgBox "You must click Cancel to exit.", 48, "You clicked Ok but entered nothing."
GoTo showInputBox
Else
MsgBox "You entered " & CancelTest & ".", 64, "Please click OK to resume."
End If
End Sub
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Tom : Thanks for the pointer ... I LIKE IT :)

I will definitly use this method from now on.

Public Sub inputExample()
TestVal = Application.InputBox("type in value.. or not", "Cancel Test")
If TestVal = False Then Exit Sub
MsgBox (TestVal)
End Sub
 

dmorse03

Board Regular
Joined
Sep 7, 2002
Messages
59

ADVERTISEMENT

That did it. Thanks.
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
I must admit that I have used the same approach as Nimrod in the past. You have converted yet another user. Preach on brother Tom!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

There are 2 versions of InputBox in VBA.

The InputBox Function is called without an object qualifiier and returns the contents of the text box or a zero-length string ("") if the user clicks Cancel.

The InputBox Method is a member of the Application object, so it is called by using Application.InputBox. It returns the contents of the text box or False if the user clicks Cancel. It is more versatile than the InputBox Function because it has a Type argument which specifies the return data type.

So Nimrod's original code (InputBox Function) is OK (provided that you don't want to do something if the user enters nothing and click OK).
 

olek1234

New Member
Joined
Oct 20, 2009
Messages
3
I have the falowing problem:
My example is:
Sub Generowanie_danych_PK1()
Dim UserVal As Currency
On Error GoTo Canceled
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) aktywów trwałych na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H107") = UserVal
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) kapitału (funduszu) własnego na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H108") = UserVal
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) zobowiązań długoterminowych na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H109") = UserVal
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) kredytów i pożyczek krótkoterminowych na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H110") = UserVal
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) amortyzacji na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H111") = UserVal
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) Zysku (straty) z działalności operacyjnej na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H112") = UserVal
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) odsetek na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H113") = UserVal
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) pozostałych kosztów finansowych na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H114") = UserVal
MsgBox ("Dane dla pełnej księgowości uzupełnione")
Canceled:
End Sub

I want stop the procedure when I click "Cancel" - but in may case this is immposible, becuse I go only to the next line.
What I should add to this example, that I'can to exit in procedure in evry moment when I click "Cancel"?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this?

Code:
If TypeName(UserVal) <> "Boolean" Then
    Range("H107") = UserVal
Else
    Exit Sub
 

olek1234

New Member
Joined
Oct 20, 2009
Messages
3
Like this?

Code:
If TypeName(UserVal) <> "Boolean" Then
    Range("H107") = UserVal
Else
    Exit Sub

I add this, and still is problem:
Sub Generowanie_danych_PK1()
Dim UserVal As Currency
On Error GoTo Canceled
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) aktywów trwałych na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H107") = UserVal
Else
Exit Sub
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) kapitału (funduszu) własnego na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H108") = UserVal
Else
Exit Sub
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) zobowiązań długoterminowych na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H109") = UserVal
Else
Exit Sub
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) kredytów i pożyczek krótkoterminowych na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H110") = UserVal
Else
Exit Sub
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) amortyzacji na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H111") = UserVal
Else
Exit Sub
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) Zysku (straty) z działalności operacyjnej na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H112") = UserVal
Else
Exit Sub
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) odsetek na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H113") = UserVal
Else
Exit Sub
UserVal = Application.InputBox(Prompt:="Podaj wartość (w zł) pozostałych kosztów finansowych na koniec ostatniego roku:", Title:="Podaj dane", Default:=Default, Type:=1)
If TypeName(UserVal) <> "Boolean" Then Range("H114") = UserVal
Else
Exit Sub
MsgBox ("Dane dla pełnej księgowości uzupełnione")
Canceled:
End Sub

I want to have the possibility the exit after of each line from application?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top