Need input box when user does not enter a number or presses cancel

[crender2000]

Need to exit sub if user clicks cancel.

If I change the variables to strings it will work. However I need the variables to be doubles.



'Declare Variables
Dim NumberOfLocations As Double
Dim isValid As Boolean
Dim FirstLocation As Double
Dim SecondLocation As Double








Sub CalculateLineShare()
' Making sure the boolean variable is set to false
isValid = False
'start of loop to make sure user puts in an integer
Do Until isValid = True
NumberOfLocations = InputBox("Enter Number of Locations", "Enter Number")
' right here is where the problem is at if the variables are strings and the user clicks cancel every thing works.
If NumberOfLocations = "" Then
Exit Sub
End If

If IsNumeric(NumberOfLocations) Then
isValid = True
Else
MsgBox "You must enter an Integer to continue"
End If


Loop
If NumberOfLocations <= 2 Then
FirstLocation = InputBox("Enter Number of Pieces used at first location", "Enter Number")
SecondLocation = InputBox("Enter Number of Pieces used at Second location", "Enter Number")
Else
End If


End Sub

[mikerickson]

Dim uiString As String

uiString = InputBox("Enter something")

If StrPtr(uiString) = 0 Then
MsgBox "cancel pressed"
Exit Sub
Else
If uiString = vbNullString Then
MsgBox "user entered null string"
Else
MsgBox "user entered " & uiString
End If
End If

[crender2000]

The variable can not be a string. It has to be a double. I can get it to work if the variable is a string.

[Kenneth Hobs]

If you need to convert a string to a double, then use cdbl().

Inputbox return value must be a string.

InputBox Function  http://www.vbaexpress.com//officeimg.vo.msecnd.net/en-us/files/255/589/ZA080010326.gif http://www.vbaexpress.com//officeimg.vo.msecnd.net/en-us/files/367/846/ZA080010327.gif http://www.vbaexpress.com//officeimg.vo.msecnd.net/en-us/files/255/589/ZA080010326.gif http://www.vbaexpress.com//officeimg.vo.msecnd.net/en-us/files/367/846/ZA080010327.gif http://www.vbaexpress.com//officeimg.vo.msecnd.net/en-us/files/238/027/ZA080010328.gif http://www.vbaexpress.com//officeimg.vo.msecnd.net/en-us/files/807/380/ZA080010329.gif http://www.vbaexpress.com//officeimg.vo.msecnd.net/en-us/files/316/121/ZA080010330.gif http://www.vbaexpress.com//officeimg.vo.msecnd.net/en-us/files/259/837/ZA080010331.gif This page is specific to the Visual Basic for Applications (VBA) Language Reference for Office 2010. Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a String (http://office.microsoft.com/client/helppreview14.aspx?AssetId=HV080006915&lcid=1033&NS=EXCEL%2EDEV&Version=14&tl=2&CTT=5&origin=HV080007592) containing the contents of the text box.

[crender2000]

I was just looking up how to convert a string to a single or a double. A single would also work. Thanks for your help.

Can I ask you one more question?

I am trying to get an if statement to work in VBA and having trouble with some thing that should be simple.


Here is the Pseudocode.

If x >1 and <= 2 then
Msgbox Here is your output
else exit sub.

The first line is not working for some reason. When I try to type the second condition <= 2 I get expected expression.
I know that this should be simple for some one who uses vba on a regular basis. I just use it once in a while. thank you so much for your help.

[mikerickson]

An alternative would be to use Application.InputBox rather InputBox


Dim uiValue As Double

uiValue = Application.InputBox("Enter", Type:=1)
If uiValue = 0 Then MsgBox "canceled": Exit Sub

MsgBox "User entered " & uiValueOne problem with that approach is that it can't distinguish between the user entering 0 and the user pressing cancel.


Other question:

Try
If 1 < x And x <= 2 Then

[crender2000]

Thank you both for you help.

I forgot that you need to restate the variable in an if statement.

I work in a factory and the people I work with do not realize that you can not remember all this syntax. They will not give us internet access they just think that you should be able to remember all this syntax. Which if I did this every day I would have a chance but some times I can go 6 months before we need to write a vba program.

[mikerickson]

A lot of syntax can be found with Excel, no external link needed. The Macro Recorder and the Object Browser are two onboard resources that I use often.