Delete Causing Error/Crash of Macro Excel Microsoft Visual Basic-CodePudding

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Target, Range("C1:C3,G1:G3,L1:L3,P1:P3")) _
      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

Pretty much the program works for automatically capitalization for the area I want. However if i press delete say if i mistyped it errors out the macro however backspace works perfectly fine with no issues

CodePudding user response：

This is a safer way to approach what you want to do - it makes sure if you try to update multiple cells it will not crash:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, rng As Range
    
    Set rng = Application.Intersect(Target, _
                  Me.Range("C1:C3,G1:G3,L1:L3,P1:P3"))
    
    If Not rng Is Nothing Then 'any cells of interest updated?
        Application.EnableEvents = False
        For Each c In rng.Cells 'check each cell
            If Not c.HasFormula Then
                c.Value = UCase(c.Value)
            End If
        Next c
        Application.EnableEvents = True
    End If
End Sub

BTW I was not seeing any errors with your posted code when selecting a cell and pressing Delete...