KeyAscii key restrictions with named range

[austin350s10]

I have been using the following code to only allow the user to enter numbers into a text box on a userform. I am needing to use a similar code to this on a named range to restrict the user from entering certain characters. I am not too sure how to manipulate the following code to make it work on a named range...

Private Sub txtFax_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  If InStr("0123456789.", Chr(KeyAscii)) = 0 Then KeyAscii = 0
End Sub

Does anyone know how to completely block particular characters from being typed into a named range?

 

[RoryA]

Short of setting a low level keyboard hook, you can't. You could just use data validation, or use the worksheet_change event to validate the data after it's entered (which is actually what I would do on the form too)

 

[austin350s10]

That was my fear....

Well I guess this brings up another question. I have a good data validation formula that worked...kinda :-/

=ISNA(MATCH(TRUE,(ISNUMBER(FIND($BH$666:$BH$675,ClientFName))),0))

This is supposed to refer to a list of invalid characters and prevent the user from entering them. Only problem is it works fine until I save and reopen the document. Once I reopen the document it stops working??

The only way I can make it work again is to remove and reapply the validation. Any ideas why this would happen?

 

[RoryA]

Did you uncheck the ignore blanks option?

 

[austin350s10]

Wow...i feel dumb. That's it!!! Thanks