Macro to force uppercase not working on specified range

发布于 2020-03-27 10:27:34

I need a macro in Excel to automatically capitalize text entered in a range of cells.

I found code online that works:

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

It functions fine, but I get a value mismatch error when I copy or paste multiple cells into the specified range. So I tried using this code instead:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Set r = Range("$A$1:$C$10")
    Application.EnableEvents = False
    For Each r In Target
        If Not r.HasFormula And r.Value <> "" Then
            r.Value = UCase(r.Value)
        End If
    Next
    Application.EnableEvents = True
End Sub

This code works perfectly even when copying and pasting, but it applies to the entire spreadsheet, even though the r variable has a range set on it. Why? Thanks!

Questioner

ahmad

Viewed

Chinese

Original

Private Sub Worksheet_Change(ByVal Target As Range) Dim Magic_Range As Range, Iterator_Range As Range Set Magic_Range = Range("$A$1:$C$10") Application.EnableEvents = False If Not Application.Intersect(Target, Magic_Range) Is Nothing Then For Each Iterator_Range In Application.Intersect(Target, Magic_Range) If Not Iterator_Range.HasFormula And Iterator_Range.Value <> "" Then Iterator_Range.Value = UCase(Iterator_Range.Value) End If Next Iterator_Range End If Application.EnableEvents = True End Sub

Macro to force uppercase not working on specified range

Related issues