VBA Input Box Question

[kb24gunner10]

1. How would I use the input box to store a value to a variable in a number format (such as double), but at the same time prevent the subroutine from crashing by using the variant data type for the variable? It always ends up saving as a string, and then why I try to compare the number to other numbers to check if they are greater or less than, the program doesn't work properly because the initial input was saved as a string.

2. Second, how do you prevent the error screen from popping up if the user doesn't enter a valid response into the input box? Is there any way to do this?

Thanks!

 

[mikerickson]

I would use the Application.InputBox. Pressing Cancel returns False.
If your variable is dimmed as double, that will be coerced to CDbl(False) i.e. 0.

Dim uiValue As Double

uiValue = Application.InputBox("enter a number", Type:=1)
If uiValue = 0 Then Exit Sub: Rem cancel pressed
MsgBox uiValue

If you want to allow 0 to be entered, you could use this. Note that the Type argument = 1 forces the entry to be numeric.

Dim uiValue As Variant

uiValue = Application.InputBox("enter a number", Type:=1)
If TypeName(uiValue) = "Boolean" Then Exit Sub: Rem cancel pressed
MsgBox uiValue

The Type argument controls what kind of entry the Application.InputBox will accept. The presence of that argument is the main advantage of Application.InputBox over simply InputBox.