Excel VBA：数据验证单元格文本联动修改的技术求助

Hey there, let's tackle this syncing issue for your data validation cells. Here's a practical VBA solution that'll keep your three Personnel/Unallocated/On Leave cells aligned exactly how you need:

Step 1: Open the VBA Editor #

Hit Alt + F11 to launch the VBA Editor. Locate your target worksheet in the left-side Project Explorer, then double-click it to open its code window.

Step 2: Paste the Sync Code #

Copy and paste this code into the worksheet's code window. Be sure to adjust the cell addresses (like B2, C2, D2) to match where your three data validation cells are located in your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Define your three target cells - UPDATE THESE ADDRESSES TO MATCH YOUR SHEET!
    Dim personnelCell As Range
    Dim unallocatedCell As Range
    Dim onLeaveCell As Range
    
    Set personnelCell = Me.Range("B2") ' Replace with your Personnel cell address
    Set unallocatedCell = Me.Range("C2") ' Replace with your Unallocated cell address
    Set onLeaveCell = Me.Range("D2") ' Replace with your On Leave cell address
    
    ' Exit if multiple cells are changed or the target isn't one of our three cells
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Union(personnelCell, unallocatedCell, onLeaveCell)) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False ' Prevent infinite loop from recursive change events
    
    Dim selectedName As String
    selectedName = Target.Value
    
    ' Sync logic: Clear the selected name from the other two cells if present
    Select Case Target.Address
        Case personnelCell.Address
            If unallocatedCell.Value = selectedName Then unallocatedCell.Value = ""
            If onLeaveCell.Value = selectedName Then onLeaveCell.Value = ""
        Case unallocatedCell.Address
            If personnelCell.Value = selectedName Then personnelCell.Value = ""
            If onLeaveCell.Value = selectedName Then onLeaveCell.Value = ""
        Case onLeaveCell.Address
            If personnelCell.Value = selectedName Then personnelCell.Value = ""
            If unallocatedCell.Value = selectedName Then unallocatedCell.Value = ""
    End Select
    
    ' Optional: Update Unallocated's data validation list to only show unassigned employees
    Dim allNames As Variant
    Dim availableNames As String
    Dim name As Variant
    
    ' Your full employee list - update this if names change
    allNames = Array("AARON MARQUIS", "AIDAN MULLIGAN", "CRAIG WILKINSON", "CRISTIANO BORTOT", _
                    "DEAN SHELFORD", "DREW JOINES", "GAVIN SHAW", "HAYDEN THOMPSON", _
                    "JAKE MOONEY", "JASON MCGLYNN", "JOHN MURPHY", "MICHAEL VOGEL", _
                    "NICHOLAS MADDERN", "SEAMUS FEE", "SEAN BLAKE", "TRENT LAWRENCE")
    
    ' Build list of names not in Personnel or On Leave
    availableNames = ""
    For Each name In allNames
        If name <> personnelCell.Value And name <> onLeaveCell.Value Then
            availableNames = availableNames & name & ","
        End If
    Next name
    
    ' Remove trailing comma if needed
    If Len(availableNames) > 0 Then availableNames = Left(availableNames, Len(availableNames) - 1)
    
    ' Update Unallocated's data validation dropdown
    With unallocatedCell.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=availableNames
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
    
    Application.EnableEvents = True ' Re-enable change events
End Sub

How This Works #

• Real-Time Sync: Whenever you select a name in any of the three cells, the code automatically clears that same name from the other two cells (so an employee can't be marked in two statuses at once).
• Dynamic Dropdown for Unallocated: The optional section updates the Unallocated cell's data validation list to only show employees who aren't already assigned to Personnel or On Leave. This keeps your options clean and avoids duplicate selections.

Important Notes #

• Save as Macro-Enabled File: You need to save your workbook as an .xlsm file (Excel Macro-Enabled Workbook) to keep the VBA code active.
• Enable Macros: When you open the file, Excel will show a security warning - click "Enable Content" to activate the sync logic.
• Update Cell Addresses/Names: Don't forget to adjust the cell addresses in the code to match your sheet, and update the allNames array if your employee list changes.

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