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

Be aware: using On Error Resume Next without turning the error handling on again is bad. — Shrotter
– Shrotter, Commented Jan 2 at 12:05

CHill60 · Accepted Answer · 2025-01-02 11:59:43Z

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

Darren Bartrup-Cook · Accepted Answer · 2025-01-02 14:12:42Z

0

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

answered Jan 2 at 14:12

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

2 Comments

Eyal Abramowitz Jan 5 at 7:19

I tried what you suggested but still get the Run-Time error 1004 when it tries to create the first validation list. On the plus side, it does seem to hold onto the variant. Fyi, the 2nd validation list is created without a problem.

Darren Bartrup-Cook Jan 5 at 18:09

I get that error on the .Unprotect line if Customer Details is password protected and I use the wrong password .Unprotect Password:="DEF". I also get it on the .Add Type:= line if the sheet is still protected when the code reaches that line, or if ListWB is an empty string. I'm guessing you're getting it because ListWB is empty - check it's holding a comma delimited list.

Collectives™ on Stack Overflow

Losing variant after using with to activate workbook

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related