Losing variant after using with to activate workbook

Question

I have a workbook with lots of code in it that I have been using for many years. One of the worksheets has code that is supposed to create and populate a validation list with the names of folder names from a specific folder. The code successfully creates a variant with a list of all the folder names, but when I use a with command to select the active workbook and create the data validation list, the variant is lost. I'll note that this had worked without issue in the past and it stopped a couple of years ago, but I have no idea why. It's also important to note that I am a beginner and either did not write most of the code I use or pieced together code from various posts. This greatly limits my ability to handle errors, so any help would be greatly appreciated!

This is the code I'm using:

Private Sub Worksheet_Activate()
'Sub Existing_client_list()

Dim SourceFolderName As String
Dim ListWB As Variant
Dim fso As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim FolderItem As Scripting.Folder

SourceFolderName = ThisWorkbook.Sheets("LUT").Range("B3").Value

Set fso = New Scripting.FileSystemObject
Set SourceFolder = fso.GetFolder(SourceFolderName)

For Each SourceFolder In SourceFolder.SubFolders
    ListWB = ListWB & "," & repalce_comma_Semi_string(SourceFolder.Name)
Next SourceFolder

With ActiveWorkbook
    .Sheets("Customer Details").Unprotect
    
    .Sheets("Customer Details").Range("C10").Select
End With

With Selection.Validation

AT THIS POINT I GET A RUN-TIME ERROR '1004': APPLICATION-DEFINED OR OBJECT-DEFINED ERROR

On Error Resume Next
.DELETE
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= \_
xlBetween, Formula1:=ListWB
.ShowError = False

End With

Set FileItem = Nothing
Set SourceFolder = Nothing
Set fso = Nothing

With ActiveWorkbook
    .Sheets("Customer Details").Unprotect
    
    .Sheets("Customer Details").Range("Report_Language").Select
End With

With Selection.Validation

On Error Resume Next
.DELETE
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= \_
xlBetween, Formula1:="=Report_Language_List"
.ShowError = False

End With
End Sub

Answer 1

You shouldn't need the On Error Resume Next - .Delete won't return an error if there's nothing to delete.

You also don't need to select or activate anything ( How to avoid using Select in Excel VBA? ).

It might also be worth adding the code to the Workbook_Open event in ThisWorkbook rather than the Worksheet_Activate event - is it likely a new folder will be created while the workbook is open?

Private Sub Worksheet_Activate()

    Dim SourceFolderName As String
    Dim ListWB As Variant
    Dim fso As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim FolderItem As Scripting.Folder
    
    SourceFolderName = ThisWorkbook.Sheets("LUT").Range("B3").Value
    
    Set fso = New Scripting.FileSystemObject
    Set SourceFolder = fso.GetFolder(SourceFolderName)
    
    For Each SourceFolder In SourceFolder.SubFolders
        ListWB = ListWB & SourceFolder.Name & ","
    Next SourceFolder
    ListWB = Left(ListWB, Len(ListWB) - 1) 'Remove final comma.
    
    'No need to select the sheet or range.... just reference it.
    With ThisWorkbook.Worksheets("Customer Details")
        .Unprotect
        With .Range("C10").Validation
            .Delete
            .Add Type:=xlValidateList, _
                 AlertStyle:=xlValidAlertInformation, _
                 Operator:=xlBetween, Formula1:=ListWB
            .ShowError = False
        End With
        
        With .Range("Report_Language").Validation
            .Delete
            .Add Type:=xlValidateList, _
                 AlertStyle:=xlValidAlertInformation, _
                 Operator:=xlBetween, Formula1:="=Report_Language_List"
            .ShowError = False
        End With
    End With
End Sub

Answer 2

Firstly the line

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= \_
xlBetween, Formula1:=ListWB

generates a syntax error. That should be

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
xlBetween, Formula1:=ListWB

i.e. no backslash. You don't need to escape the underscore.

Secondly, the section

With ActiveWorkbook
    .Sheets("Customer Details").Unprotect
    
    .Sheets("Customer Details").Range("C10").Select
End With

will cause you issues - you are trying to select a cell on a sheet, but the sheet is not active. Try this instead

With ActiveWorkbook
    .Sheets("Customer Details").Unprotect
    .Sheets("Customer Details").Select
    .Sheets("Customer Details").Range("C10").Select
End With

or better

With ActiveWorkbook.Sheets("Customer Details")
    .Unprotect
    .Select
    .Range("C10").Select
End With