Error Checking with Input Box

[kb24gunner10]

Hi,

I have an application which has a long list of prices. I have a sub that asks the user to enter a sample size out of the entire population of prices and then returns the average of a random sample of prices.

I want to use a double variable to take the price entered by the user instead of the variant type. So I have error checking turned on right before the input box stating "on error GoTo errorHandler:" and turned off right after the input box stating "on error GoTo 0" if the user enters a string into the input box rather than a number.

In the error handler, a message box displays a message saying you haven't entered a valid numeric value and then the next line says GoTo sample: (where "sample:" is the label right above the "on error GoTo errorhandler:" line is).

My problem is the error handler works fine if I enter text into the input box the first time but doesn't work the second time I enter text and it displays the usual run time error box the second time around. How would I get it to work to continuously not display the run time error box when text is entered?

I've gotten it to work using a variant data type for the variable that takes the input, but is there a way to get the error checker to work.

The code looks something like this

sample:
on error GoTo errorhandler:
sample = inputbox("enter sample size")
on error goto 0

lines of code

exit sub

errorhandler:
msgbox "enter numeric value"
GoTo sample:

end sub

Any help would be appreciated.

 

[JoeMo]

Try resetting the error number to zero after the error handler has functioned:

on error GoTo errorhandler:
sample = inputbox("enter sample size")
on error goto 0

lines of code

exit sub

errorhandler:
msgbox "enter numeric value"
Err.Clear
GoTo sample:

end sub

 

[Rick Rothstein]

Why not use the Application object's InputBox with the Type set to 1 so it will only accept a numerical value...

Dim Sample As Double
Sample = CDbl(Application.InputBox("Enter sample size...", Type:=1))

 

[kb24gunner10]

I just tried adding err.clear and it didn't work. Same thing, on the second time around, it stops at the line where the inputbox is called and gives me a run time error 13 type mismatch and highlights the input box code.

 

[kb24gunner10]

Why not use the Application object's InputBox with the Type set to 1 so it will only accept a numerical value...

Dim Sample As Double
Sample = CDbl(Application.InputBox("Enter sample size...", Type:=1))

I'll give this a try.

 

[JoeMo]

I just tried adding err.clear and it didn't work. Same thing, on the second time around, it stops at the line where the inputbox is called and gives me a run time error 13 type mismatch and highlights the input box code.

That may be b/c you placed sample:AFTER the On Error GoTo errorhandler line - it should be before that line.

 

[kb24gunner10]

That may be b/c you placed sample:AFTER the On Error GoTo errorhandler line - it should be before that line.

No, I placed it as shown in my original post, before the on error statement.

I also tried using application.input box, it works but if you press cancel it doesn't let you back out of the box, which is a minor issue that can probably be fixed.

 

[Rick Rothstein]

but if you press cancel it doesn't let you back out of the box

Did you encase the call to the Application.InputBox with the CDbl function call that I showed in the code I posted in Message #3?

 

[Rick Rothstein]

Did you encase the call to the Application.InputBox with the CDbl function call that I showed in the code I posted in Message #3?

By the way, I should mention that the code I posted in Message #3 is all the code you need... no error trapping is required as the Application.InputBox (with Type set to 1) handles the verification of the entry automatically within itself.

 

[kb24gunner10]

I ended up using the regular input box, set the data type as variant and did error checking with if statements rather than error handlers.