Force converting to UPPER CASE problems

[Sven62]

I found the following code to automatically change lower case in specified cells to upper case. It does work, however if I clear more than one cell at a time I get an error "Run-time error '13': Type mismatch."

In addition to the error message, after debugging, the code doesn't work anymore until I enable events TRUE in the Immediate Window, or close and reopen the workbook.

Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)    
If Not (Application.Intersect(Target, Range("A2:A150")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If
End Sub

 

[Rick Rothstein]

What if you use this Change event procedure instead (one benefit is that you can copy/paste multiple cells into Column A and they all will be processed)...

Private Sub Worksheet_Change(ByVal target As Range)
  If Not Intersect(target, Range("A2:A150")) Is Nothing Then
    Application.EnableEvents = False
    Range("A2:A150") = [IF(A2:A150="","",UPPER(A2:A150))]
    Application.EnableEvents = True
  End If
End Sub

 

[Sven62]

That seems to work perfectly. Google didn't know this trick. Thank You!