Visual Basic for Applications - API(?) Problem

**Matt Kutner** · 24th February 2003

I need to generate some code whereby the UserForm TextBoxes 1 & 2 must have alpha data (letters) only iinput, and will return an error if any numeric data in input. TextBoxes 3,4,5,6 and 7 must be the other way round (must be numeric, and not alpha)..

I'm not sure on how to do this, as with most things VBA

Here is the code for the particular sub procedure..

Code:

Private Sub Enter_Click()

Dim MsgboxReturn As Integer
If New_Pupil.TextBox1 = "" Then
 MsgboxReturn = MsgBox("You must enter a first name when entering a new student", vbCritical)
 GoTo ErrEntry
End If

If New_Pupil.TextBox2 = "" Then
 MsgboxReturn = MsgBox("You must enter a last name when entering a new student", vbCritical)
 GoTo ErrEntry
End If

If TextBox3.Text = "" Or Val(TextBox3.Text) < 0 Or Val(TextBox3.Text) > 30 Then
 MsgboxReturn = MsgBox("Incorrect value entered for HW1!", vbCritical)
 GoTo ErrEntry
End If

If TextBox4.Text = "" Or Val(TextBox4.Text) < 0 Or Val(TextBox4.Text) > 15 Then
 MsgboxReturn = MsgBox("Incorrect value entered for HW2!", vbCritical)
 GoTo ErrEntry
End If

If TextBox5.Text = "" Or Val(TextBox5.Text) < 0 Or Val(TextBox5.Text) > 20 Then
 MsgboxReturn = MsgBox("Incorrect value entered for HW3!", vbCritical)
 GoTo ErrEntry
End If

If TextBox6.Text = "" Or Val(TextBox6.Text) < 0 Or Val(TextBox6.Text) > 20 Then
 MsgboxReturn = MsgBox("Incorrect value entered for HW4!", vbCritical)
 GoTo ErrEntry
End If

If TextBox7.Text = "" Or Val(TextBox7.Text) < 0 Or Val(TextBox7.Text) > 50 Then
 MsgboxReturn = MsgBox("Incorrect value entered for HW5!", vbCritical)
 GoTo ErrEntry
End If

Dim ReturnResult As Integer
Dim ListboxIterator As Integer
ListboxIterator = 0
Dim RowIterator As Integer
RowIterator = 4

While RowIterator < 40
 Range("B" & RowIterator).Select
 If ActiveCell.Text = TextBox1.Text Then
  Range("C" & RowIterator).Select
    If ActiveCell.Text = TextBox2.Text Then
     ' Both names match, exit
     ReturnResult = MsgBox("Pupil Already Exists", vbCritical)
     Range("A1").Select
     GoTo ErrEntry
    End If
 End If
 RowIterator = RowIterator + 1
Wend
 
Dim Converter As Integer
Dim Converter2 As Integer
Dim Converter3 As Integer
Dim Converter4 As Integer
Dim Converter5 As Integer

'Converter converts text string entered into an integer value
Unprotect
    Rows("21:21").Select
    Selection.Insert Shift:=xlDown
    Converter = New_Pupil.TextBox3
    Converter2 = New_Pupil.TextBox4
    Converter3 = New_Pupil.TextBox5
    Converter4 = New_Pupil.TextBox6
    Converter5 = New_Pupil.TextBox7
    Converter6 = ("=SUM(D21:H21)")
    Select Case ActiveSheet.Name
        Case "GeographyClass"
            Converter7 = ("=RANK(I21, GeoMark, 0)")
        Case "LawClass"
            Converter7 = ("=RANK(I21, LawMark, 0)")
        Case "HistoryClass"
            Converter7 = ("=RANK(I21, HisMark, 0)")
    End Select
    Converter8 = ("=VLOOKUP(I21,Grades,2,TRUE)")
    Range("B21") = New_Pupil.TextBox1
    Range("C21") = New_Pupil.TextBox2
    Range("D21") = Converter
    Range("E21") = Converter2
    Range("F21") = Converter3
    Range("G21") = Converter4
    Range("H21") = Converter5
    Range("I21") = Converter6
    Range("A21") = Converter7
    Range("J21") = Converter8
    Range("A21:J21").Select

    Border
    
    ReSort
    
Protect
    New_Pupil.Hide
    Unload Me
ErrEntry:

End Sub

I'd be extremely grateful if anyone could point me in the right direction, or show me how to do it - because I genuwinely do not have a clue :/

BTW, sorry about the cheekiness of asking for help on my second ever post on this forums - but this problem IS doing my head in

**graham.edmon** · 24th February 2003

use isnumeric(cellreference) to test for being numeric, and fire an error if it comes back with false

you can loop through each of the text boxes is you create a text box collection.

without seeing more of what you are trying to do, I can't help you further.

**Matt Kutner** · 24th February 2003

I've attached a Zip. New_Pupil is the name of the userform - if yyou are able to look at it all, and help me a little further if you have the time (and patience with my lack of knowledge

)

**Matt Kutner** · 24th February 2003

I've got the IfNumeric working. IfAlpha won't work though - do I need to define the string in relation to user32.dll or something along those lines?

**Matt Kutner** · 24th February 2003

I've got it sorted now, but thank you for your time anyway. Appreciated.

**graham.edmon** · 25th February 2003

function IsAlpha(byval sInput as string) as boolean

Dim lIndex as Long

IsAlpha = True
For lIndex = 1 to Len(sInput)
If Upper(Mid(sInput, lIndex,1) ) < "A" Or Upper(Mid(sInput, lIndex,1) ) > "Z" Then
IsAlpha = False
Exit Function
End If
Next lIndex

End Function

paste this function into it and then try.. this will return True if the string is Alphabetic otherwise False.

I will have a look at your file later on if I have time.

**Matt Kutner** · 25th February 2003

I used..

Code:

Public Function isAlpha(c As String) As Boolean

IsAlpha = (c >= "a" And c <= "z") Or _
        (c >= "A" And c <= "Z")
End Function

then..

Code:

If isAlpha(New_Pupil.TextBox3) Then
 MsgboxReturn = MsgBox("No character's accepted for HW1", vbCritical)
 GoTo ErrEntry
End If

Job done..

Thanks annyway though.

**RhinoBanga** · 26th February 2003

I don't have VB here at work to try it but does that work for:

A1
a<
Z#][;][pojkm9ij09

As I don't think it would since the right hand side of the comparisons is only one character so I would think VB would only check the first character of the left hand string.

**graham.edmon** · 4th March 2003

this will only do 1 character rather than a string of characters....

Originally posted by Matt Kutner
I used..

Code:

Public Function isAlpha(c As String) As Boolean IsAlpha = (c >= "a" And c <= "z") Or _ (c >= "A" And c <= "Z") End Function

then..

Code:

If isAlpha(New_Pupil.TextBox3) Then MsgboxReturn = MsgBox("No character's accepted for HW1", vbCritical) GoTo ErrEntry End If

Job done..

Thanks annyway though.

**RhinoBanga** · 4th March 2003

So I was right then.

**graham.edmon** · 5th March 2003

Originally posted by RhinoBanga
So I was right then.

ofcourse :-) gold star to you

**graham.edmon** · 5th March 2003

Originally posted by Matt Kutner
I used..

Code:

Public Function isAlpha(c As String) As Boolean IsAlpha = (c >= "a" And c <= "z") Or _ (c >= "A" And c <= "Z") End Function

then..

Code:

If isAlpha(New_Pupil.TextBox3) Then MsgboxReturn = MsgBox("No character's accepted for HW1", vbCritical) GoTo ErrEntry End If

Job done..

Thanks annyway though.

You could use the above function with slight modifcation in the Key Press event of the text box...