如何实现仅在单元格从空白变为已填充时执行工作表变更并避免覆盖已有数据

Great question! The core issue with your current code is that in the Worksheet_Change event, the Target object represents the cell after the edit has happened. So when you check IsEmpty(Target.Value), it’s already showing the new value—meaning your condition will only trigger if the user just cleared the cell, which is the opposite of what you want.

To fix this, we need to capture the cell’s value before the edit occurred. Here’s a corrected version of your code, along with key improvements to make it robust and reliable:

Corrected VBA Code #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Exit early if multiple cells are edited (adjust if multi-cell support is needed)
    If Target.Cells.Count > 1 Then Exit Sub
    
    Dim oldValue As Variant
    Dim Description_Column_No As Integer
    Dim Default_Start As String
    Dim Output_Col_Item As Integer, Output_Col_1 As Integer, Output_Col_2 As Integer
    Dim Output_Col_3 As Integer, Output_Col_4 As Integer, Output_Col_5 As Integer
    Dim Output_Col_6 As Integer, Output_Col_7 As Integer
    
    ' Disable events to prevent recursion during undo/redo
    Application.EnableEvents = False
    
    ' Ensure events are re-enabled even if an error occurs
    On Error GoTo Cleanup

    ' Capture the original value before the user's edit
    Application.Undo
    oldValue = Target.Value
    Application.Undo ' Restore the user's input
    
    ' Only run the logic if the cell was blank before and now has content
    If IsEmpty(oldValue) And Not IsEmpty(Target.Value) Then
        ' Input parameters
        Default_Start = "N/A"
        Description_Column_No = 3 ' Adjust this to your target column number
        
        ' Calculate output columns relative to the target column
        Output_Col_Item = Description_Column_No - 1
        Output_Col_1 = Description_Column_No + 6
        Output_Col_2 = Description_Column_No + 18
        Output_Col_3 = Description_Column_No + 28
        Output_Col_4 = Description_Column_No + 38
        Output_Col_5 = Description_Column_No + 44
        Output_Col_6 = Description_Column_No + 55
        Output_Col_7 = Description_Column_No + 62
        
        ' Set the item number (correctly references the same row as Target)
        Me.Cells(Target.Row, Output_Col_Item).Value = Target.Row - 5
        
        ' Set default values for all output columns in one clean block
        With Me
            .Cells(Target.Row, Output_Col_1).Value = Default_Start
            .Cells(Target.Row, Output_Col_2).Value = Default_Start
            .Cells(Target.Row, Output_Col_3).Value = Default_Start
            .Cells(Target.Row, Output_Col_4).Value = Default_Start
            .Cells(Target.Row, Output_Col_5).Value = Default_Start
            .Cells(Target.Row, Output_Col_6).Value = Default_Start
            .Cells(Target.Row, Output_Col_7).Value = Default_Start
        End With
    End If

Cleanup:
    ' Re-enable events to restore normal worksheet behavior
    Application.EnableEvents = True
    ' Show error message if something went wrong
    If Err.Number <> 0 Then MsgBox "Error: " & Err.Description
End Sub

Key Fixes & Improvements #

1. Capture the Original Value:

   • We use Application.Undo twice: first to revert the user’s edit and get the cell’s original value, then again to restore their input. This lets us confirm the cell was blank before the edit.
   • Disabling events during this process prevents the Worksheet_Change event from triggering repeatedly.

2. Correct Range References:

   • Your original code used Target(Target.Row, Output_Col_Item) which incorrectly offsets from the top-left cell of Target. We use Me.Cells(Target.Row, Output_Col_Item) to reliably target the cell in the same row as the edited cell.

3. Error Handling:

   • The On Error GoTo Cleanup block ensures events are always re-enabled, even if an error occurs. This prevents your worksheet from getting stuck with events disabled.

4. Cleaner Default Value Setting:

   • A single With Me block replaces redundant With statements, making the code more readable and efficient.

5. Single Cell Check:

   • We exit early if multiple cells are edited, avoiding unexpected behavior from bulk edits like pasting.

This code will now only run when the user enters data into a previously blank cell in your target column, and it won’t overwrite any existing data in other columns when the target cell already had content.

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