IF NOT ISTEXT OR BLANK...

[GAZZAT5]

On a form with a textbox:

Private Sub TextBox1_Change()
If Not IsText(TextBox1.Value) Or ISBLANK(TextBox1.Value) Then
    MsgBox "" & TextBox1.Value & " is not a name. Please make sure there are no numbers, or other letters."
End If
End Sub

Returns can't find project or library "istext". Presumably this means that IsText does not exist in vb. Anyone know the correct function?

 

[GAZZAT5]

ok, that works. Presumably, i could run it from a command button click by changing the first line to
Private Sub CommandButton1_Click()

Secondly, what does the new function you have defined do? I have a vague understanding that it checks through the value, but some of the functions you used like len mid, str and Next i do not understand, since i have not needed to write functions yet.

Many thanks.

ps thanks for teaching me a new way to run code Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

 

[Norie]

It could be run from the Click event of a command button.

One advantage of using the Exit event is you can prevent the user exiting that control like this.

Cancel = True

Len - returns the length of a string

Mid(string, n, m) - returns m characters starting at the nth from a string

Next - goto the next iteration in a For loop.

 

[GAZZAT5]

yeah, but i have two textboxes, one for name and another for surname. I do use a similar code to change pages when the X - close button is clicked though.

Thanks for your help. My code is now

Function IsAlpha(str As String) As Boolean
Dim c As String
Dim I As Integer
    
    For I = 1 To Len(str)
        c = Mid(str, I, 1)
        
        Select Case Asc(c)
        
            Case 32, 65 To 90, 97 To 122
                IsAlpha = True
            Case Else
                IsAlpha = False
        End Select
        
        If Not IsAlpha Then Exit For
    Next I
End Function

Private Sub CommandButton5_Click() 'im sure
CommandButton5.Visible = False
    If Not IsAlpha(TextBox1.Value) Or TextBox1.Value = "" Then
        MsgBox "" & TextBox1.Value & " is not a name. Please make sure there are no numbers, or other symbols."
    End If
    If Not IsAlpha(TextBox2.Value) Or TextBox2.Value = "" Then
        MsgBox "" & TextBox2.Value & " is not a surname. Please make sure there are no numbers, or other symbols."
    End If
    
If IsAlpha(TextBox1.Value) Or TextBox1.Value = "" And IsAlpha(TextBox2.Value) Or TextBox2.Value = "" Then
    Dim CurRow As Long
    CurRow = ActiveCell.Row
    Cells(CurRow, 3) = TextBox2.Value
    ActiveCell = TextBox1.Value
Else
    MsgBox "Either Forename or surname is invalid. Please ensure that neither is blank, and that neither contains numbers or symbols. The staff record has not been saved."
End If
End Sub

 

[GAZZAT5]

what does the select case bit do and how does it work?

i am trying to write all this down so if i come across something requiring editing of text in a similar fashion i will be able to refer to my notes.

Thanks

 

[Norie]

GAZZA

You don't really need the third If statement in your code - just exit the sub if either textbox is invalid.

Private Sub CommandButton5_Click() 'im sure 
CommandButton5.Visible = False 
    If Not IsAlpha(TextBox1.Value) Or TextBox1.Value = "" Then 
        MsgBox "" & TextBox1.Value & " is not a name. Please make sure there are no numbers, or other symbols." 
        Exit Sub
    End If 
    If Not IsAlpha(TextBox2.Value) Or TextBox2.Value = "" Then 
        MsgBox "" & TextBox2.Value & " is not a surname. Please make sure there are no numbers, or other symbols." 
        Exit Sub
    End If 
    
    Dim CurRow As Long 
    CurRow = ActiveCell.Row 
    Cells(CurRow, 3) = TextBox2.Value 
    ActiveCell = TextBox1.Value 

End Sub

 

[Scott Huish]

It can still work, just what is being allowed needs to change, change the 33 to 32 if you don't want to allow the space character.

Private Sub TextBox1_Change()
Dim z As String
z = TextBox1.Text
Select Case Asc(Right(z, 1))
    Case 33 To 64, 91 To 96, 123 To 255
        Application.EnableEvents = False
        TextBox1.Text = Left(z, Len(z) - 1)
        Application.EnableEvents = True
End Select
End Sub