Thread: Validation Not Consistent

Hi everybody

I have a code that makes a comma separated list from a collection, and then makes a cell validation with that list.

The purpose of this code is to detect the last cell in a column, and make the next one a drop down list with unique values from the column above. get it? Its to allow the selection of the 'next value' from an intelligent list, that remembers it if it didn't exist and you had to enter it.

The code makes use of a modification of a tip by J.G. Hussey, published in "Visual Basic Programmer's Journal".

    For Each Item In NoDupes
'        UserForm1.ListBox1.AddItem Item
         Ret = Ret & Item & ";"
    Next Item

    If Ret <> ";" Then
        Ret = Left(Ret, Len(Ret) - 1)
        
        '   Show the UserForm
        '    UserForm1.Show
        

        
        With TheCell.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=CStr(Ret)
            .IgnoreBlank = False
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = False
            .ShowError = False
        End With
    End If

That is the troublesome part of the code.

The problem is that when I run this code from the Visual Basic Editor (pressing play) the functions function perfectly, and the validation returns a working list.

But when I start the code from Excel (Run Macro, or adding a Button that runs it), the validation doesn't work, and leaves a drop-down list with a unique raw value like "value1;value2;value3" and it doesn't validate it.

You could try to change semicolon with comma.
Where in you code there is ";" change it with ",".

Regards,
Antonio

Thank you both for your answers.

@royUK the purpose is to have an intelligent drop down, that picks unique values from the column that holds the validated cell, and shows them in a list. It's not for me its for an asset management firm...

@antoka05 I think that did it... However, I for some reason had allready changed from ; to , and I can't remember why (...) I guess it's wait and see. Thanks!