Test for Null with Application.InputBox?

[dplum]

I'm using the following code to get a numeric value and exit if Cancel is clicked.

Sub Get_Number()
    Dim vValue
    vValue = Application.InputBox(Prompt:="Enter a Number", Type:=1)
    If vValue = False Then Exit Sub 'Cancel was clicked
End Sub

However, when OK is clicked with no input an error is displayed that says "The formula you typed contains an error." etc... I want to eliminate this message and test for a Null value. However, the following tests have not worked.

    If vValue = "" Then Exit Sub
    If vValue = vbNull Then Exit Sub

I've also tried...

   On Error GoTo Cancelled 'placed prior to the Application.InputBox

   'with...
Cancelled:
    Exit Sub

But this does not work.

Does anyone know how to test for a Null entry?

 

[JoeMo]

Setting Type = 1 indicates you expect the user to enter a number. Since "" is not a number, the error message so indicates and forces the user to enter a number or click Cancel. That's what your code is intended to do.

If you want to test for a null value use the InputBox Function (which is different from the InputBox Method that you are currently using).

 

[dplum]

Setting Type = 1 indicates you expect the user to enter a number. Since "" is not a number, the error message so indicates and forces the user to enter a number or click Cancel. That's what your code is intended to do.

If you want to test for a null value use the InputBox Function (which is different from the InputBox Method that you are currently using).

But, there's no way to prevent the user from entering "nothing" and clicking OK -- so even though I want a numeric value, how do I test for 'nothing" being entered?

 

[JoeMo]

But, there's no way to prevent the user from entering "nothing" and clicking OK -- so even though I want a numeric value, how do I test for 'nothing" being entered?

Excel does that for you - if nothing is entered and OK is clicked, Excel checks the return, sees it is NOT a number (as you requested it be by setting Type:=1) and responds with a message to the user indicating the entry is inappropriate.

Use VBA Help to look at InputBox Function (NOT InputBox Method) and you can then test for "".

 

[dplum]

Excel does that for you - if nothing is entered and OK is clicked, Excel checks the return, sees it is NOT a number (as you requested it be by setting Type:=1) and responds with a message to the user indicating the entry is inappropriate.

Yes, if a non-numeric entry is input there is a message displayed but it is very ambiguous (talks about a 'Formula error') -- which a typical Excel user would question "What's going on?" -- So I'm looking for a way to avoid that error message.

I know that I can use InpuBox and trap errors with it, but I thought the Application.InputBox might be easier to work with, but now I'm questioning that "my easier" is unfortunately not easier for the user.

So, from what you're saying there is no way to avoid the 'Formula error' message when using Application.InputBox -- unfortunate if this is the case.

 

[JoeMo]

Yes, if a non-numeric entry is input there is a message displayed but it is very ambiguous (talks about a 'Formula error') -- which a typical Excel user would question "What's going on?" -- So I'm looking for a way to avoid that error message.

I know that I can use InpuBox and trap errors with it, but I thought the Application.InputBox might be easier to work with, but now I'm questioning that "my easier" is unfortunately not easier for the user.

So, from what you're saying there is no way to avoid the 'Formula error' message when using Application.InputBox -- unfortunate if this is the case.

I believe that is the case.