IF NOT ISTEXT OR BLANK...

GAZZAT5

GAZZAT5

Board Regular
Joined
Feb 8, 2005
Messages
69
On a form with a textbox:

Code: 
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?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Neither IsText or IsBlank are available in that way with VBA.

IsText is available via WorksheetFunction so you could try something like this.
Code: 
Private Sub TextBox1_Change()
If Not Application.WorksheetFunction.IsText(TextBox1.Value) Or TextBox1.Value = "" Then
    MsgBox "" & TextBox1.Value & " is not a name. Please make sure there are no numbers, or other letters."
End If
End Sub
 
Upvote 0
i Know a trick. I don't know the "Right" way. There is a isdate function that will return false if it is not a date. So I do a isdate on 01/01/200 concatenated with the first character. If the first character is text then it will come back false, if it is numeric... well you get the point. Work with it, it may or may not be useful to you. I actually used it to see if somehting was a number.

Code: 
If IsDate("01/01/200" & Mid(mystring, 1, 1)) = False Then
    Debug.Print "It's Text!!!!"
    Else: Debug.Print "It's not text... So saaaad."
    End If
 
Upvote 0
i want to stop people being able to enter junk like:

f8efa3$%^&yere'.][;]

for instance.
 
Upvote 0
Since you're using the Textbox Change event, as characters are being entered in the box you can check if they are valid or not, for example, this will only allow you to enter upper or lowercase letters in the box and nothing else:

Code: 
Private Sub TextBox1_Change()
Dim z As String
z = TextBox1.Text
Select Case Asc(Right(z, 1))
    Case 0 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
 
Upvote 0
hotpepper: that works, but it does not allow the user to clear the textbox using the backspace key, as it comes up with a debug error.
I wouldn't mind it if i could bypass the error by making it come up with an msgbox.


May be i could instead find out whether the textbox contains any numbers and any symbols and then create an error if it does.
 
Upvote 0
Why not validate the textbox using the Exit event rather than the change event?

What characters do you actually want to allow?
 
Upvote 0
I mean the exit event of the textbox itself.

You could try this UDF.

Code: 
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        
    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 letters."
    End If
    
End Sub


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
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Mapping a VBA variable to a Textbox value
Replies
3
Views
255
Oseitutuakrasi
O
H
  • Question
Passing Value from Sub to Userform textbox
Replies
1
Views
261
NdNoviceHlp
NdNoviceHlp
V
  • Solved
VBA search in listbox when used combobox and textbox-To be modified
2
Replies
13
Views
883
velu888
V
C
  • Question
Populate textboxes based on row number
Replies
4
Views
221
christianbiker
C
P
  • Question
If and ElseIf confusion !
Replies
5
Views
164
Micron
M

Forum statistics

Threads
1,221,867
Messages
6,162,523
Members
451,773
Latest member
ssmith04

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top