Your users will want to type in dates and numbers according to their choice of regional settings, and your code must validate those entries accordingly and maybe display meaningful error messages back to the user. This means that you have to use the Cxxx conversion functions, and the IsNumeric and IsDate validation functions.
Unfortunately, these functions all have their problems (such as not recognizing the % sign at the end of a number), which require some working around. An easy solution is to use the bWinToNum and bWinToDate functions shown at the end of this chapter to perform the validation, conversion, and error prompting for you. The validation code for a UserForm will typically be done in the OK button's Click event, and will be something like this:
Private Sub bnOK_Click() Dim dResult As Double
'Validate the number or display an error If bWinToNum(tbNumber.Text, dResult, True) Then 'It was valid, so store the number Sheet1.Range("A1").Value = dResult Else
'An error, so set the focus back and quit the routine tbNumber.SetFocus Exit Sub End If
'All OK and stored, so hide the userform Me.Hide End Sub
Was this article helpful?