CODEWITHSUNDEEP
excelvba
Claudia
Claudia

Reputation: 17

Delete text in cell subject to Worksheet_Change

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

The program automatically capitalizes the area I want. However if I press delete, if I mistyped, it errors out. Backspace works.

Upvotes: 0

Views: 68

Answers (1)

Tim Williams
Tim Williams

Reputation: 166511

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...

Upvotes: 2

Related Questions