Application.Input box validation

[bigsmooth]

I'm trying to get input from a user regarding parameters for loan calculations. I'm still very new at VBA so some of this will seem very silly, but I've been trying for hours to get this to work right and I keep fixing problems and creating more simultaneously. I've come to the conclusion that I have a fundamental misunderstanding of how this works.

This one actually works fine, but I am including it just in case I've done something less than perfect and someone can correct it.

Please Login or Register  to view this content.

This one works if I input a number, but I'd like to be able to have the validation also check for a $ and automatically trim it off. I've got it checking for cancel already but I want this input validation to catch everything and ask for corrected input on anything except $xxxx, $xxxx.xx, xxxx.xx, or xxxx (any amount of digits, not just thousands).

Please Login or Register  to view this content.

I'm not really sure what else to ask at this point. I've been searching google and these forums for a while but I can't find much in the way of examples for robust input validation.

[DonkeyOte]

If you want to be able to input $ as well as a number you would need to use the String type Application.InputBox - ie Type:=2, thereafter you can check the output is numeric by replacing "$" in the string and checking to see the remainder is numeric...ie:

Please Login or Register  to view this content.

obviously you may need to alter datatype of variable "Amount" if declared as numeric type (Double etc...)

[bigsmooth]

Thanks for your reply. I don't understand then how I would be able to check for escape or closing the input box. I need the sub to exit if they choose to quit inputting parameters, but it also needs to keep asking for input until they do it right. The way I've got it right now will not work if they input information wrong but it will cancel/escape if they choose to. If I change the variable type to something numeric, I won't be able to check for False will I? How will I know they hit escape?

[DonkeyOte]

I'm not sure I follow you - the present set-up is such that you're using Type:=1 - ie only permit numeric entry - if you want to permit end user to enter "$amount" then you must alter InputBox from Type:=1 to Type:=2

Regardless - if an end user clicks Cancel / X on the InputBox a Boolean FALSE is returned.

[bigsmooth]

Sorry for being unclear. What I'm asking is how do I check for a boolean false if the value being returned will be numeric? This doesn't work obviously but I don't get how I am supposed to test for a boolean with a string type and also somehow get a double as the final result.

Please Login or Register  to view this content.

Any advice on how to make this work and/or how to make my code neater would be greatly appreciated.

[DonkeyOte]

OK I follow you I think...

Please Login or Register  to view this content.

[bigsmooth]

Ah I see. Very elegant solution. Thank you for explaining this to me.

[bigsmooth]

I've tested this with some limited success. I want it to break if the date is before 01/01/1950 or after 01/01/2050. That part actually seems to work fine, but does this code make sense? Is there a way I can actually verify mm/yyyy? I know of the Format() function but I don't see how to use it in this case.

Please Login or Register  to view this content.

[DonkeyOte]

Makes sense to me - but are you saying you want to ensure the date always reverts to day 1 - is that why you want to check mm/yyyy format ... ie to prevent dd/mm/yyyy entry (which would go through as valid)

You could adjust the StartDate to always be 1st of month regardless of whether or not someone enteres 02/2009 or 18/02/2009 by amending the code to assigning StartDate:

Please Login or Register  to view this content.

[bigsmooth]

Makes sense to me - but are you saying you want to ensure the date always reverts to day 1 - is that why you want to check mm/yyyy format ... ie to prevent dd/mm/yyyy entry (which would go through as valid)

You could adjust the StartDate to always be 1st of month regardless of whether or not someone enteres 02/2009 or 18/02/2009 by amending the code to assigning StartDate:

Please Login or Register  to view this content.

Yes, that is exactly what I wanted. Thanks!