Thread: changing to upper case

Hi,

First of all thanks to all the forum gurus, my programming skills in excel vba has undergone a dramatic improvement. But there the odd glitches and roadblocks that I keep hitting. Look forward to more interaction with the gurus to help me move to the next level.

I have created a form in which I want the contents of cell S9 to be displayed in upper case. I have written the following code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("S9")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
End Sub

The code is embedded in the sheet code. This works fine until a user presses the DELETE to delete the contents of this cell. When that happens, I get an error message and the code stops working after that. Thereafter it just does not work even if the workbook is closed and reopened.

Is there a fix to prevent this or have I missed something in the code ? Any help will be greatly appreciated.

Thanks in advance

Anand

Maybe this...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Target.Parent.Range("S9")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    If Rng.Value = "" Then Exit Sub
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
End Sub

You're welcome Anand, thanks for the feedback and glad it worked for you