vba not containing number

[Noz2k]

How do I restrict a textbox on a form so that the user cannot enter a number into the field.

I tried using isnumeric which doesn't allow me to input "88" for instance but would allow "J88".

Would I have to use Keyascii to disallow vb0 to vb9 or something along those lines?

 

[Ruddles]

You want to stop him entering any numeric digits at all - yes?

You could loop along the string For 1 To Len(TextBox) using IsNumeric on each character, and if any of them is numeric, issue an error message and make him re-enter it.

Is he allowed to enter £$%^&*?

 

[Noz2k]

sorry for the late reply. Have been away.

Thanks for your help. In answer to your question, characters such as £$%^& would ideally not be allowed either.

It's for a Name value, so the only possible exception on that which I can think of would perhaps be a " - " when double barrel names are concerned.

 

[Noz2k]

How do I make it so that the code looks at each character individually?

I understand the To Len(textbox) procedure, but how do I specify from the first character?

Left(textbox,1) ?

 

[Noz2k]

So I haven't used the loop function, but I have banned numeric value by using this code

Private Sub Textbox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
KeyAscii = 0
Case Else
End Select
End Sub

is there a way to use the same process to restrict the user from entering !"£$%^ etc, without typing each one individually.

Also is there a way to convert the first character after a "-" to uppercase?

I have used this code to convert the first character and after a " " to convert to uppercase, but need it to do the same for double barrel names seperated by a "-"

Private Sub Textbox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
txtName.Value = StrConv(txtName, vbProperCase)
End Sub

 

[Ruddles]

Rather than disallow invalid characters, I would probably allow valid ones and disallow everything else:-

Private Sub Textbox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  Select Case KeyAscii
    Case Asc("A") To Asc("Z"), Asc("a") To Asc("z"), Asc("'"), Asc("-"), Asc(" ")
[COLOR=green]      ' alphabetic, apostrophe, hyphen or space - all ok
[/COLOR]  Case Else
[COLOR=seagreen]    [/COLOR][COLOR=green]  ' anything else not ok!
[/COLOR]    KeyAscii = 0
    End Select
End Sub

 

[Ruddles]

I have used this code to convert the first character and after a " " to convert to uppercase, but need it to do the same for double barrel names seperated by a "-"

Private Sub Textbox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
txtName.Value = StrConv(txtName, vbProperCase)
End Sub

Does it not do that? I would have thought it did.

 

[Noz2k]

No it does not unfortunately, thanks for helping with the other parts though

 

[Ruddles]

Then this:-

txtName =WorksheetFunction.Proper(txtName)

 

[Noz2k]

Brilliant, that works a treat