如何用VBA为动态名称工作表设置基于‘Data Validation’表的数据验证？

我来帮你解决这个动态工作表的数据验证问题，先看看原代码存在的几个小问题：

• 原代码会给所有非"Data Validation"的工作表都添加验证，但你只需要给那个名称动态变化的目标工作表（也就是第一个工作表）设置验证
• 固定的引用范围$A$1:$A$4无法适应"Data Validation"表中数据的增减，比如后续添加新选项时需要手动修改范围
• 警告样式用了xlValidAlertStop，会直接阻止用户输入无效值，可能过于严苛，可根据需求调整

下面是修正后的代码，完美适配你的需求：

Sub SetDynamicValidation()
    Dim wsTarget As Worksheet
    Dim wsValidationSource As Worksheet
    Dim dynamicListName As String
    
    ' 定位目标工作表（名称动态变化的第一个工作表）和数据验证源表
    Set wsTarget = ThisWorkbook.Worksheets(1)
    Set wsValidationSource = ThisWorkbook.Worksheets("Data Validation")
    
    ' 创建动态命名范围：自动包含Data Validation表A列所有非空单元格
    dynamicListName = "DynamicValidationList"
    ' 如果名称已存在，先删除旧的
    On Error Resume Next
    ThisWorkbook.Names(dynamicListName).Delete
    On Error GoTo 0
    ThisWorkbook.Names.Add _
        Name:=dynamicListName, _
        RefersTo:="=OFFSET('Data Validation'!$A$1,0,0,COUNTA('Data Validation'!$A:$A),1)"
    
    ' 给目标工作表的指定字段添加数据验证（这里以A列为例，你可以改成需要的范围）
    With wsTarget.Range("A:A").Validation
        .Delete ' 先清除原有验证规则
        .Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertWarning, ' 警告样式，可按需改为xlValidAlertInformation/xlValidAlertStop
            Operator:=xlBetween, _
            Formula1:="=" & dynamicListName
        .IgnoreBlank = True ' 允许空值，不需要的话设为False
        .InCellDropdown = True ' 显示下拉选择箭头
    End With
    
    MsgBox "数据验证已成功应用到目标工作表！", vbInformation
End Sub

关键细节说明 #

• 动态列表范围：用OFFSET+COUNTA实现自动识别"Data Validation"表A列的所有非空数据，后续添加/删除选项时无需修改代码
• 目标工作表精准定位：通过Worksheets(1)直接获取第一个工作表，不管它的名称怎么变都能正确找到
• 验证范围自定义：把wsTarget.Range("A:A")改成你需要设置验证的具体字段，比如Range("C2:C50")就是给C列第2到50行添加验证
• 警告样式调整：如果需要严格禁止无效输入，把xlValidAlertWarning改成xlValidAlertStop；要是只想提示信息，就用xlValidAlertInformation

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