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?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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).
 
Upvote 0
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"?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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
Back
Top