Data validation for symbols

  • Thread starter Thread starter jaimy
  • Start date Start date
J

jaimy

Help,

Does anyone know how to do a validation where in column a:a,
only allow alphabet, numbers, brackets not other symbols like coma,
hyphen?
Can we have more than one validation in the same cell? because I
already done one validation in column A cells.
Or how to to do it by writing codes?
Please advice. Any help would be greatly appreciated.

THANKS.
 
Data>Validation>Settings>Allow>List =MyList
OR
enetr a range of cells that contain your list
OR
enter the members of the list in the window, each separated by a comma (US).

Symbols, letters and numerals are accepted. I do not know about non-printing
characters and have not tested them.
 
The way you meant is create a reference list, is it?

But, I would allow user to enter data with format like 1999, 2003(A),
2030(1), P/2338. So, if user accidentally enter #, -, :, . an error
message will pop up to make user enter the correct data again.

I hope you understand my question.

Thanks,
 
Another option maybe to let the user type what they want and you correct them
after they hit enter.

If you like this idea, then rightclick on the worksheet tab that should have
this behavior and select View code.

Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myChar As String
Dim myValidChars As String
Dim iCtr As Long
Dim myStr As String
Dim ChangedIt As Boolean

On Error GoTo errHandler:

myValidChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789()/"

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

ChangedIt = False
With Target
For iCtr = 1 To Len(.Value)
myChar = Mid(.Value, iCtr, 1)
If InStr(1, myValidChars, myChar, vbTextCompare) = 0 Then
ChangedIt = True
Else
myStr = myStr & myChar
End If
Next iCtr

If ChangedIt Then
Application.EnableEvents = False
.Value = myStr
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

If you don't want to allow all the alphabet, just clean up this line:
myValidChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789()/"
(or add whatever other characters you want)
 
Back
Top