Pages

Thursday, August 11, 2011

Disallow Special Characters using vba in a cell

If you do not want to allow special characters to be enter into a cell. Snapshot below-






Here is the code-

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim abc As Variant
Dim chkrng As Range, cell As Range
' add or remove special charatcers here
abc = Array("!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "_", "+", "{", "}", ":", "<", ">", "?", """", "'")
' change column and range here if you want to apply it on col b , c , d, etc
If Target.Column = 1 Then
Set chkrng = Range("A" & Target.Row & ":A" & Target.Row + Target.Rows.Count)
For Each cell In chkrng
For i = LBound(abc) To UBound(abc)

If InStr(cell.Value, abc(i)) > 0 Then

MsgBox "Special Characters are not allowed", vbCritical
cell.Value = ""
Exit For
End If
Next
Next
End If
Application.EnableEvents = True
End Sub



Excel Macro http://www.filefactory.com/file/cd29849/n/disallow_spl_chars.xlsm

No comments:

Post a Comment