VBA adding cell validation failing for single cell range

Question

I have a VBA macro which gets data from another sheet as a Range object and uses that Range object to add a validation on a cell on the current sheet like this

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

This works fine except when resRng object has only one cell. What can cause a range with multiple cells to work but a single cell range to fail?

Any help would be appreciated!

Thanks!

Zerk Zerk · Accepted Answer · 2015-04-28T22:02:07

The solution to this shows how bizarre Excel can be.

Firstly, look at what is being fed into the formula1 parameter.

If a single cell is selected:

$G$2

If a range of cells is selected:

$G$2:$G$3

Infuriatingly, Excel is expecting a start and an end to your range.

The solution is to check the range size, here is an example piece of code based on yours that I used for testing that you can adjust to your needs.

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

And your code with the size check implemented:

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

VBA adding cell validation failing for single cell range

1 Answers