Preventing Invalid Characters using data Validation

[Guest]

I'm trying to create a template for users to enter data and have it input
onto a spreadsheet, there are some conditions i need to set using data
validation and im not sure how to do it.. There cannot be any
spaces,-,&,%,#... etc.. in the fields, but Numeric and Alpha are ok. There
also can only be 10 characters in the field which is easy to define. I tried
using the custom validation but I’m not familiar with how to create a formula
to achieve this goal.

 

[Guest]

Is this something that cannot be done?.. I see lots of peoples questions
being answered but not mine ..

Thanks
(e-mail address removed)

 

[Susan]

it can be done............
but it's not a real simple thing.....

one way - you'd have to have a selection_change macro that targets
those specific cells, so when they exit the cell, the maco
automatically checks what's been entered & sees if it's "ok." then it
would have to throw up a message box directing them back to the cell,
to fix it, and what's wrong with it.

an easier (but less foolproof) method would be to have a validation
message box with an input message that basically says what you said
below:

"this cell can only contain alpha or numeric characters, maximum of
10.
no spaces,-,&,%,#... etc.. in the fields"

but like i said, it won't PREVENT users from entering the wrong data.

another way - you probably could also do it with data validation & a
formula format, but i don't know how to do that, either.
try searching the newsgroup for "validation format"

susan

 

[Gord Dibben]

How about event code that strips out all but numerics or alphas as you enter
data?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
For i = 1 To Len(.Text)
sChar = Mid(.Text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP