Validation List corrupts file when saving

Question

I created a validation list with the following code:

For Each cell In DestinationWorksheet.Range("C15:C29")

    If Not cell.MergeCells Then 

        With cell.Validation 

            .Delete

            .Add Type:=xlValidateList, Formula1:=arr3 

            .IgnoreBlank = True 

            .InCellDropdown = True 

            .ShowInput = True 

            .ShowError = True 

        End With 

    End If

where arr3 is a comma separated list.i created arr3 from Join(arr, ",") where arr was an array pulled from a range of values from another workbook. It loads in fine and saves, but when i reopen the file, its corrupted. I've looked into events where I can just delete the valiadation lists before saving, but id rather keep them in so that i can convert it to an .xlsx file when i need. How can i do this?

Answer 1

You need to include the sheet name:

.Add Type:=xlValidateList, Formula1:= "='" & SourceWorksheet.Name & "'!" & SourceRangeProjects.Address

Answer 2

so if my cell range is SourceRangeProjects = SourceWorksheet.Range("A2:A" & lastCellRow) where lastcellrow is just a long, i can just call

For Each cell In DestinationWorksheet.Range("C15:C29")

    With cell.Validation 

        .Delete 

        .Add Type:=xlValidateList, Formula1:= "=" & SourceRangeProjects.Address

Is this close?

Answer 3

No it will also accept ranges. If the data is already in a one column (or one row) , just use the address of those cells prefixed with an equals sign.

Answer 4

how do i go about doing that? I currently have the range of the cells that the data is in. how can i manipulate that to get what i need? also, doesnt a validation list only allow for a comma separated list?

Answer 5

The maximum length for a validation list that is a delimited string is 255 characters. If you assign one longer than that, you will get the problem you have now. Your best bet is to put the data into a range somewhere and refer to that.