run-time error '13': type mismatch VBA 2010 to ensure uppercase text in cell

2
votes

Whats wrong with my code, every time i delete something on the worksheet it gives me a run-time error '13': type mismatch,

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Address <> "$G$13:$J$13" Then
     If Target = Range("G13") Then
          test = UCase(Target.Value)
          If test <> Target.Value Then EnsureUppercase Target
     End If
 End If

End Sub

1
vbaexcelruntime-error
On which line is the error ? – Adrien Lacroix
You need to include the code for EnsureUppercase – Alex K.
The error code was on line If Target = Range("G13") Then – Adrian Gornall

1 Answers

2
votes

Always use Error handling and Application.EnableEvents when working with Worksheet_Change event

If the code provided converts the Range("G13") to Upper Case here is more simplified code.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    Application.EnableEvents = False

    Dim rng As Range
    Set rng = Range("G13")

    If Not Intersect(Target, rng) Is Nothing Then
          Target.Value = UCase(Target.Value)
    End If

    Application.EnableEvents = True

End Sub