Numeric data only in text box on userform

C

Carlee

hi there,

I use the following code to make sure that all required fields are complete
prior to commiting data to my master log.

Question: I want to use this code to check that all numeric fields contain
only numeric data (postive, negative, whole and decimals). I have modified
the numeric field names to 'txtNear_Miss_num" to flag all numeric text boses.

Can anyone help with the code on this?


For Each ctrl In frmRaglanDailyInput.Controls
If TypeOf ctrl Is msforms.TextBox Then
If ctrl.Value = "" And ctrl.Name Like "*1*" Then
MsgBox "Missing Data in " & ctrl.Name
Exit Sub
End If
End If
Next
 
R

Rick Rothstein \(MVP - VB\)

I use the following code to make sure that all required fields are
complete
prior to commiting data to my master log.

Question: I want to use this code to check that all numeric fields
contain
only numeric data (postive, negative, whole and decimals). I have
modified
the numeric field names to 'txtNear_Miss_num" to flag all numeric text
boses.

Can anyone help with the code on this?

For Each ctrl In frmRaglanDailyInput.Controls
If TypeOf ctrl Is msforms.TextBox Then
If ctrl.Value = "" And ctrl.Name Like "*1*" Then
MsgBox "Missing Data in " & ctrl.Name
Exit Sub
End If
End If
Next

Here are two functions I've posted in the past over in the compiled VB
newsgroups (where I am originally from) and should work fine in Excel's VBA
world...

Function IsDigitsOnly(Value As String) As Boolean
IsDigitsOnly = Len(Value) > 0 And _
Not Value Like "*[!0-9]*"
End Function

Function IsNumber(ByVal Value As String) As Boolean
' Leave the next statement out if you don't
' want to provide for plus/minus signs
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9.]*" And _
Not Value Like "*.*.*" And _
Len(Value) > 0 And Value <> "." And _
Value <> vbNullString
End Function

The first is for digits only and the second is for floating point numbers
(they return True if the number is of the correct "shape"). Note the comment
in the floating point function regarding leading signs.

Rick
 
T

Tom Ogilvy

Look at the isnumeric vba function.

? isnumeric("+123.23456789")
True
? isnumeric("-123.445")
True
? isnumeric("21/7")
False
? isnumeric("")
False
? isnumeric("123.456.789")
False
? isnumeric("-3")
True
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top