Hi,

I have the following macro in excel vbe:


Code:
--------------------
Private Sub TextBox2_Change()

Dim okstop As Boolean
Dim mytext As String

okstop = False

Do
mytext = TextBox2.Value
If Not IsNumeric(mytext) And mytext < "" Then
TextBox2.Value = ""
MsgBox ("Gebruik a.u.b. alleen nummers")
Else
okstop = True
End If
Loop Until (okstop = True)

End Sub
--------------------


This macro works fine and checks every number that is typed in the
textbox. But I tried to change it to a check for text only. The macro
is below:


Code:
--------------------
Private Sub TextBox3_Change()

Dim okstop As Boolean
Dim mytext As String

okstop = False

Do
mytext = TextBox3.Value
If IsNumeric(mytext) And mytext < "" Then
TextBox3.Value = ""
MsgBox ("Gebruik a.u.b. alleen letters")
Else
okstop = True
End If
Loop Until (okstop = True)

End Sub
--------------------


The only problem is it only checks the first letter and then it quits
and you can enter numbers in it. What's wrong with the macro above and
how should it be changed to check every letter that is typed in the
textbox.
Thanks in advance for helping me!


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=557880

Not too sure about this one but i think you need to change If Not
IsNumeric to If Not IsText

regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=557880

You could give this a try:

Private Sub TextBox3_Change()

Dim okstop As Boolean
Dim mytext As String

Dim mytextLen As Integer
Dim count As Integer

okstop = False

mytextLen = Len(TextBox3.Value)

For count = 1 To mytextLen
mytext = Mid(TextBox3.Value, c, 1)
If IsNumeric(mytext) And mytext < "" Then
TextBox3.Value = ""
MsgBox ("Gebruik a.u.b. alleen letters")
End If
Next count

End Sub



leonidas wrote:
Hi,

I have the following macro in excel vbe:


Code:
--------------------
Private Sub TextBox2_Change()

Dim okstop As Boolean
Dim mytext As String

okstop = False

Do
mytext = TextBox2.Value
If Not IsNumeric(mytext) And mytext < "" Then
TextBox2.Value = ""
MsgBox ("Gebruik a.u.b. alleen nummers")
Else
okstop = True
End If
Loop Until (okstop = True)

End Sub
--------------------


This macro works fine and checks every number that is typed in the
textbox. But I tried to change it to a check for text only. The macro
is below:


Code:
--------------------
Private Sub TextBox3_Change()

Dim okstop As Boolean
Dim mytext As String

okstop = False

Do
mytext = TextBox3.Value
If IsNumeric(mytext) And mytext < "" Then
TextBox3.Value = ""
MsgBox ("Gebruik a.u.b. alleen letters")
Else
okstop = True
End If
Loop Until (okstop = True)

End Sub
--------------------


The only problem is it only checks the first letter and then it quits
and you can enter numbers in it. What's wrong with the macro above and
how should it be changed to check every letter that is typed in the
textbox.
Thanks in advance for helping me!


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=557880

Private Sub ComboBox1_Change()

If IsNumeric(Right(ComboBox1.Text, 1)) Then

ComboBox1.Text = Left(ComboBox1.Text, Len(ComboBox1.Text) - 1)

End If


End Sub




in your case
isNumeriec ("a111") if false
so after entering first letter everything else is not numeric and you
can type what you want.


play with .text, not .value





leonidas wrote:
Hi,

I have the following macro in excel vbe:


Code:
--------------------
Private Sub TextBox2_Change()

Dim okstop As Boolean
Dim mytext As String

okstop = False

Do
mytext = TextBox2.Value
If Not IsNumeric(mytext) And mytext < "" Then
TextBox2.Value = ""
MsgBox ("Gebruik a.u.b. alleen nummers")
Else
okstop = True
End If
Loop Until (okstop = True)

End Sub
--------------------


This macro works fine and checks every number that is typed in the
textbox. But I tried to change it to a check for text only. The macro
is below:


Code:
--------------------
Private Sub TextBox3_Change()

Dim okstop As Boolean
Dim mytext As String

okstop = False

Do
mytext = TextBox3.Value
If IsNumeric(mytext) And mytext < "" Then
TextBox3.Value = ""
MsgBox ("Gebruik a.u.b. alleen letters")
Else
okstop = True
End If
Loop Until (okstop = True)

End Sub
--------------------


The only problem is it only checks the first letter and then it quits
and you can enter numbers in it. What's wrong with the macro above and
how should it be changed to check every letter that is typed in the
textbox.
Thanks in advance for helping me!

Trap it on input

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57:
KeyAscii = 0
End Select
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"leonidas" wrote in
message ...

Hi,

I have the following macro in excel vbe:


Code:
--------------------
Private Sub TextBox2_Change()

Dim okstop As Boolean
Dim mytext As String

okstop = False

Do
mytext = TextBox2.Value
If Not IsNumeric(mytext) And mytext < "" Then
TextBox2.Value = ""
MsgBox ("Gebruik a.u.b. alleen nummers")
Else
okstop = True
End If
Loop Until (okstop = True)

End Sub
--------------------


This macro works fine and checks every number that is typed in the
textbox. But I tried to change it to a check for text only. The macro
is below:


Code:
--------------------
Private Sub TextBox3_Change()

Dim okstop As Boolean
Dim mytext As String

okstop = False

Do
mytext = TextBox3.Value
If IsNumeric(mytext) And mytext < "" Then
TextBox3.Value = ""
MsgBox ("Gebruik a.u.b. alleen letters")
Else
okstop = True
End If
Loop Until (okstop = True)

End Sub
--------------------


The only problem is it only checks the first letter and then it quits
and you can enter numbers in it. What's wrong with the macro above and
how should it be changed to check every letter that is typed in the
textbox.
Thanks in advance for helping me!


--
leonidas
------------------------------------------------------------------------
leonidas's Profile:
http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=557880

Hi,

Thanks a lot for all your help!
I have one final question and that is where I can find a list with all
the casenumbers for all keys on the keyboard.
I now know that 48 to 57 is (1234567890), but I would like to know the
rest too.
Thanks in advance!


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=557880

It just conforms to the ASCII value. You can see them all at
http://www.lookuptables.com/, or just type it in the immediate window in the
VBIDE, such as

?ASC("A")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"leonidas" wrote in
message ...

Hi,

Thanks a lot for all your help!
I have one final question and that is where I can find a list with all
the casenumbers for all keys on the keyboard.
I now know that 48 to 57 is (1234567890), but I would like to know the
rest too.
Thanks in advance!


--
leonidas
------------------------------------------------------------------------
leonidas's Profile:
http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=557880