validating data input in Excel UserForm 2

[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.

 

[SkipVought]

Hi,

As I suggested in your previous thread, create a table (on a sheet would be good) that describes the FORMATTING and VALIDATION that you will need for each textbox.

The table might have headings like...
[tt]
CtrName
DataType
DataFormat
DataValidation
[/tt]
Read the table into an array, or traverse the table each time you check a textbox.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[crawfme]

Sorry, Skip, this is still over my head. This is my first time creating a form and I'm muddling my way through with the help of Tek-Tips, Google, and a copy of Excel 2003 by Walkenbach (which I believe you had recommended in another post).

I think I understand the concept of what you're suggesting, but the actual how-to escapes me. Currently I have a number of routines like this:

'check for valid dates
If Not IsDate(txtPurchDt) Then
Me.txtPurchDt.SetFocus
MsgBox "Please enter a valid purchase date (mm/dd/yyyy)"
Exit Sub
Else
Me.txtPurchDt.Value = Format(Me.txtPurchDt.Value, "mm/dd/yyyy")
End If

I think you're suggesting a table like this:

CtrName DataType DataFormat DataValidation
txtPurchDt Date "mm/dd/yyyy" > '01/01/2008'
txtIntRate Numeric "00.00%" between 0 and 100
txtAmount Numeric "$999,999.99" between 0 and 1000000

I'm not even entirely sure how to read the table into an array and I have no idea how I would check each textbox against it. I'll continue to do some research on my end, but if you have any further insight, I'd appreciate it.

Thanks.

 

[fumei]

I know nothing about data validation in Excel. If this is fairly easy to do, then that would be the way to go. As it stands:

"Do I have to write code for EVERY textbox to check if it's numeric data if it's supposed to be? "

Without some other means of validation, the answer to that is...yes you do.

Error trapping / data validation is the hardest of all things, IMO. Rock solid validation can take up to 50% of the work.

The problem, as you have discovered, with using textboxes is that they contain text. User entered text. And users can put in any crap they care to.

You may want to see if you can cut down on the amount of text entered. You say you have used dropdowns and optionbuttons. Perhaps a calendar for dates, rather than a textbox?

faq219-2884

Gerry
My paintings and sculpture

 

[Andrzejek]

For Dates, I would suggest using DatePicker, MonthViewer or some other Calendar control, instead of just simple textBox with a lot of validation. With this other controls you don't have ANY validation because user can not chosse invalid date. Sweet.

For text boxes that should have just numeric values, try:

Private Sub Text1_KeyPress(KeyAscii As Integer)

Select Case KeyAscii
    Case 48 To 57, [blue]8[/blue]
        [green]'just numbers[/green], [blue]backspace[/blue]
    Case Else
        KeyAscii = 0
        MsgBox "Numeric values only"
End Select

End Sub

If you need to include a period, as in 3.14159, include KeyAscii = 8 and use IsNumeric to make sure user did not entered 3.14.15


Have fun.

---- Andy