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?

 

[WJ Young]

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

This code works exactly as needed, thanks.

Interesting function, StrPtr. Undocumented, which according to some means I won't be able to count on it in future releases.


Still, I wonder why Access doesn't allow the use of the application.inputbox method. Here are my references:

• Visual Basic for Applications
• Microsoft Access 14.0 Object Library
• OLE Automation
• Microsoft Excel 14.0 Object Library
• Microsoft Office 14.0 Object Library
• Microsoft ActiveX Data Objects Recordset 2.8 Library
• Microsoft DAO 3.6 Object Library

Any suggestions?

 

[Rick Rothstein]

Interesting function, StrPtr. Undocumented, which according to some means I won't be able to count on it in future releases.

I do not think I would not worry about it disappearing.

http://support.microsoft.com/kb/199824

Here is some unofficial documentation...

http://vb.mvps.org/tips/varptr.asp

 

[shg]

Any suggestions?

No, sorry, I don't use Access, but there are lots of people here that do.

 

[Polanskiman]

Hello,

I am not a VB expert so please be patient with me.

I have a probem with several of my forms. They do work well except when I click the 'OK' button but no value has been entered in the textboxes:

---------------------------------------------------------------
This one returns a: Run-time error '13': Type mismatch
---------------------------------------------------------------

Public Sub ShowFormIRRTable()
    Application.ScreenUpdating = False
    Dim fmForm As FrmIRRTable
    Set fmForm = New FrmIRRTable
    With fmForm
        .Show
        If Not .Cancel Then
            Range("C23").Value = .InputReturn
            Range("C24").Value = .InputReturn + (.InputReturn2 * 1)
            Range("C25").Value = .InputReturn + (.InputReturn2 * 2)
            Range("C26").Value = .InputReturn + (.InputReturn2 * 3)
            Range("C27").Value = .InputReturn + (.InputReturn2 * 4)
            Range("C28").Value = .InputReturn + (.InputReturn2 * 5)
            Range("C29").Value = .InputReturn + (.InputReturn2 * 6)
            Range("C30").Value = .InputReturn + (.InputReturn2 * 7)
            Range("C31").Value = .InputReturn + (.InputReturn2 * 8)
            Range("C32").Value = .InputReturn + (.InputReturn2 * 9)
            Range("C33").Value = .InputReturn + (.InputReturn2 * 10)
            Range("C34").Value = .InputReturn + (.InputReturn2 * 11)
            Sheets("DATA").Range("F46").Value = .InputReturn3 & "% "
            Call F2_Populate_IRR_BOI_0_5_8
            Call G2_Conditional_Formating_IRR_Table
        Application.ScreenUpdating = True
        MsgBox "Internal Rate of Return has been calculated according to the desired prices." & vbCr & "" & vbCr & "Proceed to IRR Table for Results"
        End If
    End With
    Unload fmForm
    Set fmForm = Nothing
        Call ShowFormSensiAna
End Sub

-------------------------------------------------------------------------------------------------
This one continues with the macro if OK button is pressed even if the textbox is empty
-------------------------------------------------------------------------------------------------

Public Sub ShowFormSensiAna()
    Application.ScreenUpdating = False
    Dim fmForm As FrmSensiAna
    Set fmForm = New FrmSensiAna
    With fmForm
        .Show
        If Not .Cancel Then
            Range("S15").Value = .InputReturn
        Call I3_Sensitivity_Analysis_BOI_and_no_BOI
        Call I4_Sensitivity_Analysis_LPG_SPECs_BOI
        Application.ScreenUpdating = True
        Sheets("PRICES").Select
        Range("G41").Select
        Selection.Copy
        Range("G21").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
        MsgBox "You have chosen the following value for your Sensitivity Analysis: " & vbCr & "" & vbCr & .InputReturn & "USD/ton" & vbCr & "" & vbCr & "Please proceed to 'SENSITIVITY ANALYSIS' Tab and 'LPG SENSITIVITY' Tab for results."
        End If
    End With
    Unload fmForm
    Set fmForm = Nothing
End Sub

How can I correct this behavior?

Thanks a lot for the help anyone is able to provide.

 

[Andrew Poulsom]

Try with the Val function, eg:

    Range("C23").Value = Val(.InputReturn)
    Range("C24").Value = Val(.InputReturn) + (Val(.InputReturn2) * 1)

 

[Polanskiman]

I must have missed this.

Thank you 8 months later!!

Try with the Val function, eg:

    Range("C23").Value = Val(.InputReturn)
    Range("C24").Value = Val(.InputReturn) + (Val(.InputReturn2) * 1)

 

[daniellouw]

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

An alternative is:

Radius = InputBox(“Specify the radius of the circle.”)
If StrPtr(Radius) = 0 Then
  End     ' The user pressed the cancel button
else if Radius = ""
  msgbox("You have not typed in anything. Program will be closed.")
  end
else
  Area = WorksheetFunction.Pi * R^2
end if

 

[CelineT]

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

oh great, learned something new again! makes indeed a big difference.