Dropdownlist with own formula as source?

[MichiZH]

Hi guys, my first post

I want to create a dropdown list as validation, with only certain worksheet names in my workbook. So I created an own formula, which lists all the worksheet names I want:

Public Function GetAllWorksheetNames() As String

    Dim strResult As String
    Dim boolSettingsFound As Boolean
    Dim cintWS As Integer
    
    
    boolSettingsFound = False
    For cintWS = 1 To Sheets.Count
        If Sheets(cintWS).Name <> strcWorksheets_Settings Then
            strResult = strResult & Sheets(cintWS).Name & ";"
        Else
            boolSettingsFound = True
        End If
    Next cintWS
    
    GetAllWorksheetNames = Left(strResult, Len(strResult) - 1)
End Function

And if I put now under validation, list the source as =getallworksheetnames() I get an error. Is this even possible?

I tried another approach where I write all worksheet names directly in a hidden column as soon as the respective sheet opens. And this was the source for the dropdown. However (since it was dynamic?) as soon as the entries changed from the original state, the list source didn't work anymore...

 

[Andrew Poulsom]

Welcome to MrExcel.

What is strcWorksheets_Settings?

 

[MichiZH]

Thank you sir

Ah thats just the worksheet name. The formula works great. The output in a cell is for example:

Sheet1;Sheet2;Sheet3 But this doesn't work directly as source for the list?

<tbody>
</tbody><colgroup><col></colgroup>

 

[MichiZH]

Thank you sir

Ah thats just the worksheet name. The formula works great. The output in a cell is for example:

Sheet1;Sheet2;Sheet3

<tbody>
</tbody>

But this doesn't work directly as source for the list?

 

[Andrew Poulsom]

ADVERTISEMENT

You haven't declared or assigned anything to the variable strcWorksheets_Settings.

 

[MichiZH]

Yes I did. Thats a public variable in my module. The formula does work like this with the ouput posted above. However this doesn't work in the dropdown validation list...

 

[Andrew Poulsom]

Sorry, you can't use a UDF in Data Validation.