Page 1 of 3 123 LastLast
Results 1 to 10 of 26
Like Tree1Likes

Test for InputBox 'Cancel'

This is a discussion on Test for InputBox 'Cancel' within the Excel Questions forums, part of the Question Forums category; After an InputBox statement, how do I test to see if user pressed "Cancel" and if so, exit the Sub?...

  1. #1
    Board Regular
    Join Date
    Sep 2002
    Posts
    59

    Default

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

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default

    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

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,984

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default

    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


    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  5. #5
    Board Regular
    Join Date
    Sep 2002
    Posts
    59

    Default

    That did it. Thanks.

  6. #6
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962

    Default

    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!
    Silly Billy was here....

    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! *****************

  7. #7
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,388

    Default

    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).
    marc j likes this.

  8. #8
    New Member
    Join Date
    Oct 2009
    Location
    Warszawa
    Posts
    3

    Default Re: Test for InputBox 'Cancel'

    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"?

  9. #9
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,388

    Default Re: Test for InputBox 'Cancel'

    Like this?

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

  10. #10
    New Member
    Join Date
    Oct 2009
    Location
    Warszawa
    Posts
    3

    Default Re: Test for InputBox 'Cancel'

    Quote Originally Posted by Andrew Poulsom View Post
    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?

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com