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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,817
Messages
5,772,456
Members
425,760
Latest member
paphon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top