CODEWITHSUNDEEP
excelvba
V.Hunon
V.Hunon

Reputation: 320

Excel VBA Create Dropdown List on Cell based on Worksheetnames

I want to create a dropdownlist based on worksheetnames.

Worksheets("MainSheet").Range("A1").Select

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= ***WorkSheetnames***
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

So where ***WorkSheetnames*** is, I wanted to put all worksheetnames

Maybe something with 

for i = 1 to application.sheets.count 
'Store data into array?
Names = sheets(i).name
next

How can I do this without adding a sheet or something..

Upvotes: 1

Views: 48

Answers (1)

Ahmed AU
Ahmed AU

Reputation: 2777

you are almost near goal

Before your code add

Dim Shts As String
    For i = 1 To Sheets.Count
        Shts = Shts & ThisWorkbook.Sheets(i).Name & ","
    Next
    Shts = Left(Shts, Len(Shts) - 1)

then within validation make

Formula1:=Shts

Upvotes: 2

Related Questions