如何实现跨工作表动态Data Validation下拉列表自动更新？

核心思路 #

通过工作表Change事件监听「3」工作表中C8:C1000区域的内容变化，一旦有新增或修改操作，自动调用你的setupDV宏更新「4」工作表C8单元格的下拉列表。

具体实现步骤 #

1. 打开「3」工作表的代码窗口

   • 右键点击「3」工作表的标签，选择「查看代码」。

2. 添加工作表Change事件代码
   在弹出的代码窗口中粘贴以下代码：

   Private Sub Worksheet_Change(ByVal Target As Range)
       ' 定义需要监听的目标区域
       Dim watchRange As Range
       Set watchRange = Me.Range("C8:C1000")
       
       ' 检查修改的单元格是否在监听范围内
       If Not Intersect(Target, watchRange) Is Nothing Then
           ' 禁用事件防止循环触发（批量操作时更稳定）
           Application.EnableEvents = False
           ' 调用原宏更新下拉列表
           setupDV
           ' 恢复事件监听
           Application.EnableEvents = True
       End If
   End Sub
   

3. 优化原setupDV宏（可选）
   原代码逻辑没问题，可补充变量显式声明、简化判断逻辑，提升可读性：

   Sub setupDV()
       Dim rSource As Range, rDV As Range, r As Range, csString As String
       Dim c As Collection
       Dim v As Variant ' 显式声明变量v
       
       Set rSource = Sheets("3").Range("C8:C1000")
       Set rDV = Sheets("4").Range("C8")
       Set c = New Collection
       csString = ""
       
       On Error Resume Next
       For Each r In rSource
           v = r.Value
           If v <> "" Then
               c.Add v, CStr(v)
               If Err.Number = 0 Then
                   ' 简化字符串拼接逻辑
                   csString = IIf(csString = "", v, csString & "," & v)
               Else
                   Err.Number = 0
               End If
           End If
       Next r
       On Error GoTo 0
       
       With rDV.Validation
           .Delete
           .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=csString
           .IgnoreBlank = True
           .InCellDropdown = True
           .InputTitle = ""
           .ErrorTitle = ""
           .InputMessage = ""
           .ErrorMessage = ""
           .ShowInput = True
           .ShowError = False
       End With
   End Sub
   

关键注意点 #

• 确保setupDV宏存放在标准模块中（不是工作表或ThisWorkbook模块），否则工作表事件无法正常调用它。
• 加入Application.EnableEvents = False/True是为了避免批量粘贴内容时重复触发事件，导致程序卡顿。

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