将下拉列表添加到使用VBA动态创建的工作簿

Dim wb As Workbook
Set wb = Workbooks.Add

Dim rng As Range
Set rng = wb.Worksheets(1).Range("A1")

With rng.Validation
    .Delete ' 删除先前的数据验证
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Apple, Banana, Orange"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = "选择水果"
    .ErrorTitle = "错误"
    .InputMessage = "请选择一个水果"
    .ErrorMessage = "请选择列表中的一个值"
    .ShowInput = True
    .ShowError = True
End With

wb.SaveAs "路径\文件名.xlsx"
wb.Close