Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngB As Range
Dim CellB As Range
Dim RngCE As Range
Dim CellCE As Range
Const TextPatternB As String = "*[!A-Z]*"
Const TextPatternCE As String = "*[!0-9A-Z]*"
' See if anything entered/copied into column
Set RngB = Intersect(Target, Range("B4:B28"))
Set RngCE = Intersect(Target, Range("C4:C28"))
'----------------------------------------------------------------------------------------------------------------
' Exit if nothing put in watched column
If RngB Is Nothing Then Exit Sub
' Loop through updated values in watched range
For Each CellB In RngB
' See if length exceeds 3
If Len(CellB) > 3 Then
Application.EnableEvents = False
CellB.Value = Left(CellB, 3)
Application.EnableEvents = True
MsgBox "Entry in cell " & CellB.Address(0, 0) & " limited to 3 characters", vbOKOnly, "WARNING!"
End If
' See if characters are in UPPERCASE
If CellB.Text Like TextPatternB Then
Application.EnableEvents = False
CellB.Value = ""
Application.EnableEvents = True
MsgBox "Entry in cell " & CellB.Address(0, 0) & " should be in UPPERCASE only", vbOKOnly, "WARNING!"
End If
Next
'----------------------------------------------------------------------------------------------------------------
' Exit if nothing put in watched column
If RngCE Is Nothing Then Exit Sub
' Loop through updated values in watched range
For Each CellCE In RngCE
' See if length exceeds 34
If Len(CellCE) > 34 Then
Application.EnableEvents = False
CellCE.Value = Left(CellCE, 34)
Application.EnableEvents = True
MsgBox "Entry in cell " & CellCE.Address(0, 0) & " limited to 34 characters", vbOKOnly, "WARNING!"
End If
' See if characters are in UPPERCASE
If CellCE.Text Like TextPatternCE Then
Application.EnableEvents = False
CellCE.Value = ""
Application.EnableEvents = True
MsgBox "Entry in cell " & CellCE.Address(0, 0) & " should be in UPPERCASE & Numbers only", vbOKOnly, "WARNING!"
End If
Next
End Sub