Code: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
Interesting function, StrPtr. Undocumented, which according to some means I won't be able to count on it in future releases.
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
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
Range("C23").Value = Val(.InputReturn)
Range("C24").Value = Val(.InputReturn) + (Val(.InputReturn2) * 1)
Try with the Val function, eg:
Code:Range("C23").Value = Val(.InputReturn) Range("C24").Value = Val(.InputReturn) + (Val(.InputReturn2) * 1)
After an InputBox statement, how do I test to see if user pressed "Cancel" and if so, exit the Sub?
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
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).