求助：实现单元格历史录制，超N行或达100/1000条时停止的宏功能

Got it, let's get that stop logic working for your cell history macro! Since you already have code writing entries to new rows, adding the count check is straightforward. I'll show you two reliable approaches, depending on your needs.

Approach 1: Check Existing Row Count Directly #

This method counts the number of existing records in your history sheet every time a change happens. It's simple and doesn't require extra variables, making it great for quick setups.

Modified Code Example #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Set your maximum record limit here (swap 100 for 1000 if needed)
    Const MAX_RECORDS As Integer = 100
    
    ' Only track changes to your target cell (update "$A$1" to your specific cell)
    If Target.Address <> "$A$1" Then Exit Sub
    
    ' Count existing records in the history sheet (replace Sheet2 with your sheet name)
    Dim recordCount As Long
    recordCount = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Stop recording if we hit the limit
    If recordCount >= MAX_RECORDS Then
        MsgBox "Max record limit (" & MAX_RECORDS & ") reached. Stopping history recording."
        ' Disable events to prevent unwanted triggers (optional but recommended)
        Application.EnableEvents = False
        Exit Sub
    End If
    
    ' Write the new entry to the next empty row
    Dim nextRow As Long
    nextRow = recordCount + 1
    Sheet2.Cells(nextRow, 1).Value = Target.Value
End Sub

Approach 2: Use a Module-Level Counter (More Efficient) #

If you're dealing with larger limits like 1000 records, this method is faster. It tracks the count with a variable instead of scanning the entire sheet every time a change occurs.

Step 1: Declare Variables at the Top of Your Module #

Put this outside of any subroutine (at the very top of your VBA module):

Private recordCounter As Integer
Const MAX_RECORDS As Integer = 1000 ' Adjust your limit here

Step 2: Update the Change Event #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Only track your target cell
    If Target.Address <> "$A$1" Then Exit Sub
    
    ' Check if we've hit the max count
    If recordCounter >= MAX_RECORDS Then
        MsgBox "Max record limit reached. Stopping recording."
        Application.EnableEvents = False
        Exit Sub
    End If
    
    ' Write the new entry to the history sheet
    Dim nextRow As Long
    nextRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Sheet2.Cells(nextRow, 1).Value = Target.Value
    
    ' Increment the counter after writing the entry
    recordCounter = recordCounter + 1
End Sub

Bonus: Add a Reset Function #

If you need to restart recording later, add this subroutine to reset the counter and clear existing history (optional):

Sub ResetCellHistoryRecorder()
    recordCounter = 0
    ' Clear existing history (adjust the range to match your setup)
    Sheet2.Range("A2:A" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
    ' Re-enable events if they were disabled earlier
    Application.EnableEvents = True
    MsgBox "Recorder reset. You can start recording again."
End Sub

Key Notes #

• Replace Sheet2 with the actual name of your history worksheet.
• Update $A$1 to the cell you're monitoring for changes.
• The Application.EnableEvents = False line prevents the macro from triggering itself when writing to the history sheet, and ensures recording stops once the limit is hit.

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