Data Validation

[tiredofit]

This code creates a data validation list for the cell A1:

Dim i, j


    For j = 1 To 10
    
        i = i & "," & j
        
    Next j
    
    With Sheet1.Range("A1")
    
        With .Validation
        
            .Delete
            .Add Type:=xlValidateList, _
                 AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, _
                 Formula1:=i
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = vbNullString
            .ErrorTitle = vbNullString
            .InputMessage = vbNullString
            .ErrorMessage = vbNullString
            .ShowInput = True
            .ShowError = True
        
        End With
           
    End With

However if the loop goes to 100, saving the file then opening it reveals an error.

Excel found unreadable content in myfile.xlsm. Do you want to recover the contents of the workbook? If you trust the source of this workbook, click Yes.

I think the reason is there is a 250 character limit to a data validation cell.


But if I created the DV where it links to a source, for example if the numbers 1 to 100 were on Sheet2 and I point the DV to that column of data, it seems fine.


Why is that?


Does anyone know if this "limit" problem applies to ActiveX Comboboxes?