通过VBA阻止Excel文件在指定单元格未填充时保存或关闭

Got it, let's tackle this problem step by step. You want to stop users from saving or closing your workbook if required columns in a new row of the tb_Action_Tracker table aren't filled—specifically when they pick a status from the K column dropdown in the "Action Tracker" sheet. Here's a robust, user-friendly implementation:

1. First, Define Your Required Columns #

Before writing code, list out which columns are mandatory (you mentioned "specified columns" but didn't name them, so I'll use placeholders like "Task Name", "Owner" in the code—swap these with your actual required column names later).

2. Add a Worksheet Change Event (Monitor K Column Selections) #

We need to detect when a user selects a status in the K column of your table. This triggers our save/close validation checks.

Open the VBA Editor with Alt + F11, find the "Action Tracker" sheet in the Project Explorer (left pane), and paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tbl As ListObject
    Dim changedCell As Range
    
    ' Link to your table
    Set tbl = Me.ListObjects("tb_Action_Tracker")
    
    ' Check if the edited cell is in the "Current Status" column (K) and is a single cell
    If Not Intersect(Target, tbl.ListColumns("Current Status").Range) Is Nothing And Target.Cells.Count = 1 Then
        ' Only flag for validation if a status was selected (cell isn't empty)
        If Target.Value <> "" Then
            ' Use a hidden workbook name to track if we need to enforce checks (avoids false triggers)
            On Error Resume Next
            ThisWorkbook.Names.Add Name:="EnforceChecks", RefersTo:=True, Visible:=False
            On Error GoTo 0
        End If
    End If
End Sub

3. Add Workbook-Level Events (Block Save/Close) #

Next, we'll hook into the workbook's save and close events to run our validation. Double-click ThisWorkbook in the Project Explorer and paste this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Reuse the same validation logic for both close and save actions
    Cancel = Not ValidateRequiredColumns()
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = Not ValidateRequiredColumns()
End Sub

Private Function ValidateRequiredColumns() As Boolean
    Dim tbl As ListObject
    Dim lastRow As ListRow
    Dim reqCols As Variant
    Dim col As Variant
    Dim cellVal As Variant
    
    ' Check if validation is needed (set by the worksheet change event)
    On Error Resume Next
    Dim enforceChecks As Boolean
    enforceChecks = ThisWorkbook.Names("EnforceChecks").RefersToRange.Value
    On Error GoTo 0
    
    If Not enforceChecks Then
        ValidateRequiredColumns = True ' No check needed—allow save/close
        Exit Function
    End If
    
    ' Link to your table
    Set tbl = ThisWorkbook.Worksheets("Action Tracker").ListObjects("tb_Action_Tracker")
    
    ' Replace these with YOUR required column names from the table
    reqCols = Array("Task Name", "Owner", "Due Date")
    
    ' Target the last row (the new one the user just added/edited)
    Set lastRow = tbl.ListRows(tbl.ListRows.Count)
    
    ' Check each required column in the new row
    For Each col In reqCols
        cellVal = lastRow.Range(tbl.ListColumns(col).Index).Value
        If cellVal = "" Or IsEmpty(cellVal) Then
            ' Show a clear error message to the user
            MsgBox "Oops! Please fill in the required column: " & col & " in your new action row.", vbExclamation, "Missing Required Data"
            ValidateRequiredColumns = False ' Block save/close
            Exit Function
        End If
    Next col
    
    ' If all checks pass, clear the validation flag
    On Error Resume Next
    ThisWorkbook.Names("EnforceChecks").Delete
    On Error GoTo 0
    
    ValidateRequiredColumns = True ' Allow save/close
End Function

4. Customize the Code for Your Workflow #

• Update Required Columns: In the ValidateRequiredColumns function, replace the reqCols array with your actual mandatory column names (match exactly what's in your table header).
• Tweak Error Messages: Adjust the MsgBox text to fit your team's terminology or add more context.
• Validate All Rows: If you need to check every row where K column has a status (not just new ones), modify the function to loop through all ListRows instead of just the last one.

Key Tips #

• Save your workbook as a Macro-Enabled Workbook (.xlsm)—otherwise the VBA code won't persist or run.
• Test thoroughly: Add a new row, select a status from K column, leave a required column blank, and try saving/closing to confirm the block works.
• If users might add multiple rows before saving, you can expand the validation to check all rows with a selected status but empty required columns.

内容的提问来源于stack exchange，提问作者Adéla Škrábalová