基于Excel行首状态单元格内容锁定指定行内单元格的技术需求问询

Nice work starting with the VBA foundation—let’s tweak it to fully satisfy all your locking rules. The key thing to remember about Excel cell locking is that cells are locked by default, but the locking only takes effect when the worksheet is protected. Here’s a polished solution that checks all your boxes:

1. Updated VBA Code #

Replace your existing Locking sub with this version, which handles all three requirements explicitly:

Sub LockRowsBasedOnState()
    Dim ws As Worksheet
    Dim cell As Range
    Dim stateRange As Range
    
    ' Set your target worksheet (update the name if it's not the active sheet)
    Set ws = ThisWorkbook.Worksheets("YourSheetName") ' Replace with your sheet name
    
    ' Step 1: Unlock all cells first (critical for controlled locking)
    ws.Cells.Locked = False
    
    ' Define the range of State cells (A3:A612 as per your original code)
    Set stateRange = ws.Range("A3:A612")
    
    ' Step 2: Loop through each State cell to apply locking rules
    For Each cell In stateRange
        Select Case UCase(cell.Value) ' Use UCase to ignore case differences
            Case "ARCHIVED"
                ' Lock the entire row, then unlock the State cell (A column)
                cell.EntireRow.Locked = True
                cell.Locked = False ' Ensure State is always editable
            Case "ONGOING"
                ' Unlock the whole row first, then lock only AA & AB columns
                cell.EntireRow.Locked = False
                ws.Range("AA" & cell.Row & ":AB" & cell.Row).Locked = True
                cell.Locked = False ' Double-check State stays editable
            Case "BAD-PRODUCT" ' Match your actual "Bad-product" status value
                ' Unlock the entire row for full editing
                cell.EntireRow.Locked = False
            ' Add additional case statements here if you have other statuses
            Case Else
                ' Default: unlock entire row for any unlisted statuses
                cell.EntireRow.Locked = False
        End Select
    Next cell
    
    ' Step 3: Protect the worksheet to activate locking (uncomment below)
    ' ws.Protect Password:="YourSecurePassword", UserInterfaceOnly:=True
    ' UserInterfaceOnly lets VBA modify locked cells without unprotecting first
End Sub

2. Key Code Explanations #

• Unlock All Cells First: Excel’s default cell state is locked, but this doesn’t do anything until the sheet is protected. By unlocking everything upfront, we can explicitly set locking only where needed.
• Case-Insensitive Checks: Using UCase(cell.Value) ensures the code works even if someone types "archived" (lowercase) instead of "Archived".
• Archived Status: Locks the entire row but immediately unlocks the State cell (column A) to keep it editable.
• Ongoing Status: Unlocks the whole row first, then targets just the AA and AB columns for locking—perfect for your specific rule.
• Worksheet Protection: The UserInterfaceOnly:=True parameter is a game-changer: it lets VBA adjust locking settings without requiring you to unprotect the sheet every time.

3. Automate Locking (Optional) #

To make this hands-off, add this event handler to your worksheet’s code module. It will automatically run the locking macro whenever someone edits the State column:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if the edited cell is in the State column range (A3:A612)
    If Not Intersect(Target, Me.Range("A3:A612")) Is Nothing Then
        LockRowsBasedOnState ' Trigger the locking macro
    End If
End Sub

4. Final Setup Tip #

1. Replace "YourSheetName" in the main macro with your actual worksheet name.
2. Uncomment the ws.Protect line and set a password if you want to enforce locking for all users.
3. Test by changing the State value in a row—you’ll see the locking rules apply immediately once the sheet is protected.

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