data verification in Excel UserForm

[crawfme]

I have designed a UserForm that gathers over 40 pieces of information required to perform an annuity calculation. The data includes dates, dollar amounts, percentages, other numeric data (such as the term in years), and text information (such as name).

I have 2 questions:

1. Is there an easy way to format the data? For example, if a user keys in 2.5 as a percentage, I would like the form to show 2.5%. I think I read somewhere that you don't want to have an OnClick event for all the fields as it will slow the application down, but it also seems to be a nightmare in terms of the amount of code there is. (I also ultimately want to save the data to a spreadsheet as a value that will be used in the calculation i.e. as 0.025.)

2. I am in the process of writing code to verify that the data makes sense. I have used used some dropdown lists and radio buttons for some of the input, but a lot of the data is being entered in textboxes. Do I have to write code for EVERY textbox to check if it's numeric data if it's supposed to be? Or to check that the data is a valid date? The amount of code that I'm writing seems to be getting out of hand.

I appreciate any advice. Thanks.

 

[johnwm]

Userform = VBA. Try forum707 for better results - this forum is for VB5/6 only

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[SkipVought]

With regard to, "Do I have to write code for EVERY textbox to check if it's numeric data if it's supposed to be? Or to check that the data is a valid date? The amount of code that I'm writing seems to be getting out of hand."

Create a Format/Validation Table. Read it into an array. Use the array parameters to format/validate a textbox. It will greatly reduce your lines of code and the number of procedures.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[crawfme]

Thanks, Skip. I am very intrigued by your suggestion, but I'm not quite sure how to begin. When you suggest creating a format/validation table, where would that exist? Is that on a worksheet?

Would you have a simple example of what you're describing that I could build on?

As suggested by johnwm, I will re-post my question in Forum 707. Thanks.