Add Validation list in VBA Excel 97/2003 (.xls)

stroll11

New Member
Joined
Feb 27, 2019
Messages
1
Hi guys! I'm currently working on a macro where I need to populate a validation list for a cell. The list comes from a string that's joins elements from an array. Here's the code:

Code:
    If Not IsArrayEmpty(Clas) Then
        For i = PrimeraClase To UltCla
            Clase = CStr(Ana.Cells(i, ColAnaCla))
            With Ana.Range(LetraColumna(Val(ColAnaClaPiv)) & i).Validation
                .Delete
            End With
            ListConcat = ""
            For j = LBound(Clas) To UBound(Clas) - 1
                If Clas(j) <> Clase Then
                    ListConcat = ListConcat & Clas(j) & Application.International(xlListSeparator)
                End If
            Next j
            If Clas(UBound(Clas)) <> Clase Then
                ListConcat = ListConcat & Clas(UBound(Clas))
            End If
            Debug.Print ListConcat
            
            With Ana.Range(LetraColumna(Val(ColAnaClaPiv)) & i).Validation
                Sleep 100
                .Add xlValidateList, xlValidAlertStop, Formula1:=ListConcat
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        Next i
    Else
        For i = PrimeraClase To UltCla
            With Ana.Range(LetraColumna(Val(ColAnaClaPiv)) & i).Validation
                .Delete
            End With
        Next i
    End If

There's no error in the code, instead the list sometimes displays wrong. For example, given values "a", "b" and "c":

Case OK: If value in cell A1 is "a", then in B2 displays the items "b" and "c"; if "b" then "a" and "c"... and so on.
Case not OK: If value in cell A1 is "a", then in B2 displays the item "b,c"; if "b" then it shows "a,c"... and so on.

Randomly, the list displays either of both cases above. I must use "xls" file format (in case this is an issue), since there's info collected from SharePoint's lists.

¿Is there a way to avoid this randomness?


Thanks in advance!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,960
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top