单工作表多宏集成问题：日期更新正常但大写转换失效求助

Let's break down why your uppercase conversion isn't working, then fix the code properly.

The Key Issues in Your Original Code #

1. Syntax/Logical Error on the Else If Line:
   Your Else If Application.EnableEvents = False line is both syntactically off (ElseIf should be one word) and logically backwards. After the date update block, you explicitly set Application.EnableEvents = True, so this condition will never be true—meaning your uppercase code never runs at all.
2. Inefficient Full-Range Loop:
   Even if the code ran, looping through every cell in A10:D1000,G10:J1000,T10:T1000 every time any cell changes is slow, and could trigger infinite event loops if you don't disable events properly when modifying cell values.
3. No Target Check for Uppercase Region:
   You should only process cells that were actually changed (the Target range) if they fall within your uppercase conversion area, instead of processing the entire range every time.

Corrected Code #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Dim UpperCaseRng As Range
    Dim UpperCaseCell As Range
    
    ' --- Date Update Logic ---
    xOffsetColumn = 20
    Set WorkRng = Intersect(Me.Range("J:J"), Target) ' Use Me instead of ActiveSheet for reliability
    
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False ' Disable events to prevent loop
        For Each Rng In WorkRng
            If Not VBA.IsEmpty(Rng.Value) Then
                Rng.Offset(0, xOffsetColumn).Value = Now
                Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
            Else
                Rng.Offset(0, xOffsetColumn).ClearContents
            End If
        Next
        Application.EnableEvents = True ' Re-enable events
    End If
    
    ' --- Uppercase Conversion Logic ---
    ' Define the range where we want uppercase text
    Set UpperCaseRng = Intersect(Me.Range("A10:D1000,G10:J1000,T10:T1000"), Target)
    
    If Not UpperCaseRng Is Nothing Then
        Application.EnableEvents = False ' Disable events before modifying cells
        Application.Calculation = xlCalculationManual ' Speed up large updates
        For Each UpperCaseCell In UpperCaseRng
            ' Only convert if the cell contains text (skip numbers/errors)
            If VarType(UpperCaseCell.Value) = vbString Then
                UpperCaseCell.Value = UCase(UpperCaseCell.Value)
            End If
        Next
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True ' Re-enable events
    End If
End Sub

What We Changed & Why #

• Fixed the Event Flow: Separated the date and uppercase logic into two independent checks, so each runs only when the modified cell falls in its target range.
• Used Me Instead of ActiveSheet: This ensures we're always working with the sheet that contains the event code, not whatever sheet happens to be active (more reliable).
• Targeted Processing: We only process the cells that were actually changed (Intersect with Target) instead of looping through thousands of cells every time.
• Added Safety Checks: We check if the cell contains a string before converting to uppercase, so we don't accidentally mess up numbers or error values.
• Proper Event/Calculation Toggling: Disabled events before modifying cells to prevent infinite loops, and temporarily turned off automatic calculation to speed up processing for large ranges.

内容的提问来源于stack exchange，提问作者Tomasz Kozuch