Worksheet_Change event not working in cells with data validation

**scanxiety** · 10-26-2015, 07:44 AM

Hi;

I am a 'try it til it works' VBA user, with an unfortunately sketchy idea of what's really going on. I have a table with data validation to ensure only appropriate entries are made. However, anyone may simply type an entry that is spelled the same but does not have the proper case, thus my attempt as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If LCase(Target.Value) = "not in class" Or LCase(Target.Value) = "not rated" Or LCase(Target.Value) = "did not submit" Then
Target.Value = Application.Proper(Target.Value)
Else: Target.Value = UCase(Target.Value)
End If

End Sub

The code works fine outside of the cells with data validation applied, but crashes with feeling in the data validation cells. Please help?

**TMS** · 10-26-2015, 08:05 AM

Try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rInterest As Range
Set rInterest = Me.Range("A2:A20")  ' modify to suit

If Intersect(Target, rInterest) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

Application.EnableEvents = False
    If LCase(Target.Value) = "not in class" _
    Or LCase(Target.Value) = "not rated" _
    Or LCase(Target.Value) = "did not submit" _
    Then
        Target.Value = Application.Proper(Target.Value)
    Else
        Target.Value = UCase(Target.Value)
    End If
Application.EnableEvents = True
End Sub

Regards, TMS

**TMS** · 10-26-2015, 08:09 AM

See the example workbook attached.

Oh, and please use Code Tags

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**scanxiety** · 10-26-2015, 08:34 AM

Thanks heaps for the quick response. I will have a go at this tomorrow night - getting a bit late here. Apologies re: the code tags, and thanks for your explanation of what's needed in that area. I will let you know how I go.