Excel VBA运行时错误1004（调整大小时触发）及防跳行代码求助

Hey Craig, let's work through that Runtime Error 1004 with your Resize call and get your row-locking logic working smoothly. Here's what's likely causing the issue, plus a fixed implementation tailored to your needs:

That error usually pops up for one of these reasons:

• Bad range parameters: If you used negative numbers, incorrect column counts, or an invalid starting cell in your Resize call, Excel can't interpret the range you want.
• Worksheet protection conflicts: If your sheet is protected but you haven't granted VBA permission to modify locked cells, trying to unlock rows will fail.
• Unclear range references: Not explicitly tying your ranges to the current worksheet can make VBA misinterpret which cells you're targeting.

Here's a revised version of your Worksheet_Change event that avoids the Resize pitfall and solidifies your row-locking logic:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim currentRow As Long
    Dim ws As Worksheet
    Dim unlockRowRange As Range
    
    ' Prevent infinite event loops
    Application.EnableEvents = False
    
    ' Tie all ranges to the current worksheet
    Set ws = Me
    currentRow = Target.Row
    
    ' Only trigger for changes in C:K, below your header row (adjust row 1 if needed)
    If Not Intersect(Target, ws.Range("C:K")) Is Nothing And currentRow > 1 Then
        ' Check if the current row meets your 6-filled-cells requirement
        If ws.Cells(currentRow, "W").Value = 6 Then
            ' Define the next row's C:K range directly (no Resize needed)
            Set unlockRowRange = ws.Range("C" & currentRow + 1 & ":K" & currentRow + 1)
            
            ' Unlock the range for editing
            unlockRowRange.Locked = False
            
            ' Optional: Lock all previous C:K rows to enforce sequential entry
            ' ws.Range("C2:K" & currentRow).Locked = True
        End If
    End If
    
    ' Re-enable event handling
    Application.EnableEvents = True
End Sub

1. No more Resize confusion: Instead of relying on Resize, we build the target range directly using row numbers (e.g., C3:K3 for the row after row 2). This eliminates parameter mix-ups that cause 1004 errors. If you still want to use Resize, the correct syntax would be ws.Range("C" & currentRow + 1).Resize(1, 9) (since C to K is 9 columns) — your original error was likely a wrong column count here.
2. Explicit worksheet reference: Set ws = Me ensures all cell references point to the active sheet, avoiding cross-sheet mix-ups.
3. Event loop protection: Application.EnableEvents = False stops the change event from triggering itself when you modify cell locks, preventing crashes.
4. Targeted trigger: Intersect ensures the code only runs when someone edits cells in C:K, so unrelated changes don't waste resources.

1. Worksheet protection: For this to work, you need to protect your sheet with the UserInterfaceOnly flag (letting VBA modify locks while users can't). Add this to your workbook's Workbook_Open event, or include it in the worksheet code:

   ' Add this at the top of your Worksheet_Change event (before other logic)
   If Not ws.ProtectContents Then
       ws.Protect Password:="your-secure-password", UserInterfaceOnly:=True
   End If
   

2. W列公式验证: Double-check your W column formula counts only the current row's filled cells. Use =COUNTA(C2:K2) for row 2, then drag it down — this correctly counts non-blank cells in C:K for each row.
3. Header row adjustment: If your header isn't in row 1, update the currentRow > 1 check to match your actual header row number.

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