excel - VBA为单个单元格范围添加单元格验证失败

Question

我有一个 VBA 宏，它从另一个工作表中获取数据作为 Range 对象，并使用该 Range 对象在当前工作表上的单元格上添加验证，如下所示

With Cells(c.Row, colResource).Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:="=" & Sheets(sheetResources).Name & "!" & resRng.Address
    .InCellDropdown = True
End With

这工作正常，除非 resRng对象只有一个单元格。
什么会导致具有多个单元格的范围工作但单个单元格范围失败？

任何帮助，将不胜感激!

谢谢!

Answer 1

对此的解决方案显示了 Excel 是多么奇怪。

首先，查看输入 formula1 的内容。范围。

如果选择了单个单元格:

$G$2

如果选择了一系列单元格:

$G$2:$G$3

令人生气的是，Excel 期待您的范围的开始和结束。

解决方案是检查范围大小，这是我用于测试的基于您的示例代码，您可以根据需要进行调整。

Sub add_validation()

Dim rng As Range
Set rng = Selection


With Cells(1, 1).Validation
    .Delete
    If Selection.Count = 1 Then
        .Add Type:=xlValidateList, Formula1:="=" & ActiveSheet.Name & "!" & rng.Address & ":" & rng.Address
    ElseIf Selection.Count > 1 Then
        .Add Type:=xlValidateList, Formula1:="=" & ActiveSheet.Name & "!" & rng.Address
    End If
    .InCellDropdown = True
End With

End Sub

并且您的代码实现了大小检查:

With Cells(c.Row, colResource).Validation
    .Delete
    If resrng.Count = 1 Then
        .Add Type:=xlValidateList, Formula1:="=" & Sheets(sheetResources).Name & "!" & resrng.Address & ":" & resrng.Address
    ElseIf resrng.Count > 1 Then
        .Add Type:=xlValidateList, Formula1:="=" & Sheets(sheetResources).Name & "!" & resrng.Address
    End If
    .InCellDropdown = True
End With