Excel VBA根据工作表名称在单元格上创建下拉列表

Question

我想根据工作表名称创建一个下拉列表。

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

那么在哪里 ***WorkSheetnames***是，我想把所有工作表名称

也许有什么

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

我怎么能在不添加工作表或其他东西的情况下做到这一点..

Answer 1

你快接近目标了

在您的代码添加之前

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

然后在验证中

Formula1:=Shts