Using Excel I am having a devil of a time trying to get the included code to work. I am attempting to restrict the values entered into a cell to only "A/B/C" They can be entered in either upper or lower case. If the value is entered in lower case then this script does successfully change the value to upper case.
The coding falls over when a value other than "A/B/C" is entered.
What I am trying to achieve is the following:
I have remarked out the line that fails.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("C9:C51")) Is Nothing Then
Else
With Target
If UCase(.Text) <> .Text Then
.Value = UCase(.Text)
' ElseIf .Text < "A" Or .Text > "C" Then .Value = ""
End If
End With
End If
End Sub
Can anyone suggest a solution to this problem ?
Perhaps:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("C9:C51")) Is Nothing Then Else With Target If UCase(.Text) <> .Text Then .Value = UCase(.Text) If .Value = "A" Or .Value = "B" Or .Value = "C" Then Else .ClearContents End If End If End With End If End Sub
EDIT#1:
Based on the comment below, this should be used instead:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("C9:C51")) Is Nothing Then
Else
With Target
Application.EnableEvents = False
.Value = UCase(.Text)
If .Value = "A" Or .Value = "B" Or .Value = "C" Then
Else
.ClearContents
End If
Application.EnableEvents = True
End With
End If
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments