VBA Input Box Question

kb24gunner10

New Member
Joined
Sep 29, 2014
Messages
9
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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,905
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.
Code:
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.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,489
Messages
5,596,455
Members
414,069
Latest member
StudExcel

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