Excel二级动态下拉菜单联动问题：VBA Validation.Add报错排查

核心问题根源 #

• 数据验证的Formula1参数引用跨工作表范围时，必须使用带工作表名称的绝对引用格式（'工作表名'!$A$1:$A$10），直接用Range.Address只会返回单元格地址，不带工作表前缀，导致数据验证无法识别跨表范围。
• 未清除目标单元格已有的数据验证规则，重复添加时触发冲突报错。
• 原代码中Range(name)的引用依赖当前工作表上下文，若命名范围在其他工作表，容易出现引用歧义。

修正后的VBA代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅响应行业选择单元格的变更
    If Not Intersect(Target, Me.Range("ChooseIndustry")) Is Nothing Then
        Dim targetCell As Range
        Dim namedRange As Range
        Dim namedRangeName As String
        
        ' 指定需要设置下拉的目标单元格（跨工作表）
        Set targetCell = ThisWorkbook.Worksheets("Customer Input").Range("B4")
        ' 拼接对应行业的全局命名范围名称
        namedRangeName = Me.Range("B2").Value & "_locations"
        
        ' 尝试获取全局命名范围，避免因名称不存在报错
        On Error Resume Next
        Set namedRange = ThisWorkbook.Names(namedRangeName).RefersToRange
        On Error GoTo 0
        
        ' 确认命名范围存在后执行操作
        If Not namedRange Is Nothing Then
            ' 先清除目标单元格原有数据验证规则
            targetCell.Validation.Delete
            ' 添加新的下拉验证，使用带工作表前缀的跨表引用格式
            targetCell.Validation.Add _
                Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Formula1:="='" & namedRange.Parent.Name & "'!" & namedRange.Address
        End If
    End If
End Sub

关键修复说明 #

• 用ThisWorkbook.Names(namedRangeName).RefersToRange直接获取全局命名范围，避免工作表上下文干扰。
• 添加验证前执行Validation.Delete，清除旧规则防止冲突。
• 构造Formula1时，通过namedRange.Parent.Name获取命名范围所在工作表名称，拼接成数据验证可识别的跨表引用格式。
• 增加错误捕获逻辑，防止因命名范围不存在导致程序崩溃。

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