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:

Please Login or Register  to view this content.

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]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/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.