Test for Null with Application.InputBox?

dplum

Board Regular
Joined
Nov 1, 2006
Messages
216
I'm using the following code to get a numeric value and exit if Cancel is clicked.
Code:
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.
Code:
    If vValue = "" Then Exit Sub
    If vValue = vbNull Then Exit Sub

I've also tried...
Code:
   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?
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Nov 1, 2006
Messages
216
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Nov 1, 2006
Messages
216
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,483
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top