- Code: Select all
ivate Sub Worksheet_Change(ByVal Target As Range)
' the cell being monitored
If Target.Address <> "$H$21" Or Target.Value = "" Then Exit Sub
' unprotect sheet
ActiveSheet.Unprotect
' prevent this procedure from calling itself
Application.EnableEvents = False
' change what's in the cell
Target.Value = onlyNumbers(Target.Value)
Target.NumberFormat = "000-00-0000"
' re-enable events
Application.EnableEvents = True
' re-protect sheet
ActiveSheet.Protect
End Sub
Private Function onlyNumbers(myVal As Variant) As Variant
Dim i As Integer
For i = 1 To Len(myVal)
If IsNumeric(Mid(myVal, i, 1)) Then onlyNumbers = onlyNumbers & Mid(myVal, i, 1)
Next i
End Function
Is there something, I am missing, or doing wrong?