text boxes 3

[drewdaman]

hello,

i have a form in excel with some text boxes. is there a good way to make sure that the value entered in a text box is an integer? sorry, not very familiar with vba! i do not want a dropdown list or anything.. i want the user to be able to enter any integer.

thanks.

 

[drewdaman]

let me expand a bit.

if i use Cint, there will be an error if i try to convert an invalid entry to an int. then i can use a goto. but all my experience in codign tells me that using a goto is bad practice!

any other suggestions?

 

[rmikesmith]

Not sure why you need to resort to a GoTo, unless you're referring to error trapping (e.g. On Error Goto label). I usually validate all data entries when the user selects the OK button (or eqivalent). If validation fails, a message is generated and the Userform remains on screen. If validation passes, the data is stored and the Userform dismissed.

In this case, my validation routine would use error suppression and testing to see if the input was indeed an integer. Example:

On Error Resume Next
intVar = CInt(TextBox1.Text)
If Err.Number <> 0 Then
  'Input is not an integer
   Err.Clear
End If
On Error GoTo 0 (or other error handler)

Regards,
Mike

 

[PHV]

You may also consider the IsNumeric function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[drewdaman]

great!

that's exactly what i was looking for.. thanks very much mike and phv

 

[drewdaman]

but now i have another related question! (sorry!!)

some of hte vales that i have to get from teh user are quite large (>1000000). now, if i try to assign this to an int, then it crashes.. saying that there is an overflow. but if i use a long or a double, the user can enter decimal numbers. i don't what that to be the case either. any solutions?

thanks!

 

[PHV]

Dim lngVar As Long
If IsNumeric(TextBox1.Text) Then
lngVar = CLng(TextBox1.Text)
Else
' invalid entry stuff
End If


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[drewdaman]

well.. that's the point.. how do i check if the user has entered a decimal part to the value?

ie. textbox has 100000.112
then clng will convert that to a long

but, i would consider that an invalid entry.. i need to ensure that the user enters a number without a decimal point.

thanks!

 

[DaveInIowa]

Expanding on what PHV had written:

    Dim lngVar As Long
    If (IsNumeric(TextBox1)) Then
        If (CLng(TextBox1) = CDbl(TextBox1)) Then
            lngVar = CLng(TextBox1)
        Else
            'Value is a number but not an integer
        End If
    Else
        'Value is not a number
    End If

 

[drewdaman]

awesome! thanks a lot.. i would never have guessed that you have to compare a long to a double to see if you have any values after the decimal point :s

 

[PCLewis]

Well you can't actually compare a long to a double. What you don't see under the hood there is that VBA converts the long to a double then compares two doubles. So you really have:

If (CDbl(CLng(TextBox1)) = CDbl(TextBox1)) Then

To allow the user to enter any integer however you'll need to treat the entry as a string. You never mentioned whether you actually want to use the number they enter. So if all you ant to do is check that the number is an integer, then something simple like the following would do it too.

Private Sub CommandButton1_Click()
  Dim lStart As Long
  Dim c As Long
  Dim bNotInt As Boolean
  
  If Left(TextBox1.Text, 1) = "-" Then
    lStart = 1
  End If
  
  For c = lStart + 1 To Len(TextBox1.Text)
    If Not (Mid(TextBox1.Text, c, 1) >= "0" And Mid(TextBox1.Text, c, 1) <= "9") Then
      bNotInt = True
      Exit For
    End If
  Next
  
  If bNotInt Then MsgBox "Not an Int"
End Sub