excel - When i add this code in workbook received an error

Question

I have below code which is create a data validation for a range but whenever i add this code in the workbook. Received this error "we found a problem with some content" while opening the workbook but when i remove this from workbook then it works fine.

and when i click on save the updated data in workbook it gives the option to select a folder to save the file.

Can someone please look into this that what is the issue with the code.

Dim list1               As Range
    Application.EnableEvents = False
    Dim validationFormula   As String

    Set list1 = Sheet3.Range("A1:A100")

    Dim myCell As Range
    For Each myCell In list1
        If Not IsEmpty(myCell) Then
            validationFormula = validationFormula & myCell.Value2 & ","
        End If
    Next

    validationFormula = validationFormula & Chr(160)

    With Sheet1.Range("C4:C2000").Validation
        .Delete
        .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=validationFormula
        .IgnoreBlank = False
        .InCellDropdown = True
end with

question from:https://stackoverflow.com/questions/65907038/when-i-add-this-code-in-workbook-received-an-error

Answer 1

Try the next way, please:

   Dim list1 As Range
    Application.EnableEvents = False
    Dim validationFormula As String

    Set list1 = Sheet3.Range("A1:A100")
    validationFormula = Join(Application.Transpose(Application.index(list1, 0, 1)), ",")

    With Sheet1.Range("C4:C2000").Validation
        .Delete
        .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=validationFormula
        .IgnoreBlank = False
        .InCellDropdown = True
   End With
   Application.EnableEvents = True
End Sub