Test for InputBox 'Cancel'

[dmorse03]

After an InputBox statement, how do I test to see if user pressed "Cancel" and if so, exit the Sub?

 

[Andrew Poulsom]

Sorry, I missed an End If:

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

 

[olek1234]

Sorry, I missed an End If:

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

Still is error. After Else must be condition "If false".
Else is without If.

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:", Type:=1)
If TypeName(UserVal) <> "" Then Range("H107") = UserVal
Else
Exit Sub
End If

 

[Andrew Poulsom]

You can only continue after Then if there is no Else or End If. You need 5 lines not 4, like I posted.

 

[Hatye]

Hi,

I can see that it has been a while since this thread was active, but I hope someone can help me with this little thing regarding cancelling an InputBox

I use this code:

Dato = InputBox("Dato for måling" & Chr(13) & Chr(13) & "Skrives i formatet" & Chr(13) & "01.02.2011 for 1.feb.2011", , Date)
If Dato = False Then Exit Sub

It is an inputbox for date. The default text is today's date.
What can I do to make it cancel if the cancel button is hit?

 

[Andrew Poulsom]

If the user clicks Cancel, the InputBox function returns a zero-length string (""). S try:

If Dato = "" Then Exit Sub

Note that Excel's InputBox returns False if the user clicks Cancel. For that you would use Application.InputBox.

 

[Hatye]

If the user clicks Cancel, the InputBox function returns a zero-length string (""). S try:

If Dato = "" Then Exit Sub

Note that Excel's InputBox returns False if the user clicks Cancel. For that you would use Application.InputBox.

This worked out great!

Thank you so much

 

[WJ Young]

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).

Andrew, anyone,

I type "Application." and the InputBox method is not in the list of available methods/functions. Any ideas why that would be? My VBA is in Access (2002-2003 file format in 2010 program version).

 

[shg]

In Excel VBA, Application refers to the Excel; so it appears (I don't use Access) that Access doesn't provide the method.

Can you not just use the InputBox function?

 

[WJ Young]

In Excel VBA, Application refers to the Excel; so it appears (I don't use Access) that Access doesn't provide the method.

Can you not just use the InputBox function?

No, I need to use the method so I can distinguish between "Cancel" and an empty "OK" response.

 

[shg]

Sub x()
    Dim sInp        As String
 
    sInp = InputBox("Enter something")
 
    If StrPtr(sInp) = 0 Then
        MsgBox "User pressed Cancel"
    ElseIf Len(sInp) Then
        MsgBox "User entered """ & sInp & """ and pressed OK"
    Else
        MsgBox "User entered nothing and pressed OK"
    End If
End Sub