Excel data validation problem when validation list > 256 characters

[deboyz]

Hi,

I have a worksheet where all of my data validation values are maintained and I use code to read from this worksheet and set the validation on a different worksheet. I have to do this because Excel 97 requires that the data validation range be on the same worksheet as the range being validated. I have run into a problem when the list I pass is greater than 256 characters (see below, Formula1:=Formula). When this happens the validation list that appears in the cell being validated will show #VALUE! I don't understand why this is happening since the variable that stores the list is a variant and the help documentation states that Formula1 is a variant. Does anyone have any ideas?

Thanks!

Here is the VBA code that sets the validation:

Private Sub SetValidation(TargetRange As Range, Formula As Variant)
    With TargetRange.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlEqual, Formula1:=Formula
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Pick an item from the list."
        .ErrorMessage = "Pick an item from the list."
        .ShowInput = True
        .ShowError = True
    End With
End Sub

 

[xlbo]

Well, this may not completely solve the problem but it may help. Validation lists DON'T have to be on the same worksheet as the validation cells

If you NAME the range, you can use the name as your validation list and it doesn't matter what worksheet it is on (well, in the same workbook would probably be useful)

eg list of values in sheet1!A1:A10
name the range myRange
in sheet2, set up data validation>List
in the textbox, enter =myRange
voila
This should help you out as your list won't need to be concatenated together and therefore the 256 char error won't be a problem Rgds
~Geoff~

 

[deboyz]

Thank you very much, this is working great!