0

Using VBScript to control an Excel workbook, I am able to add 1 item to the drop-down list. However, I am not sure how to further add more items. This is my code:

Set excelAppObj = CreateObject("excel.appliction")
Set workbookObj = excelAppObj.Workbooks.Add
Set worksheetObj = workbookObj.Sheets(1)
With worksheetObj.Range("A1").Validation
    .Add 3, 1, 1, "test1"
    .IgnoreBlank = True
    .InCellDropDown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
user692942
  • 16,398
  • 7
  • 76
  • 175
  • 3
    Does this answer your question? [how to add a drop down list in excel using vbs](https://stackoverflow.com/questions/21788845/how-to-add-a-drop-down-list-in-excel-using-vbs) – user692942 Nov 09 '22 at 08:35
  • @user692942 I have read up on that post, but that posts adds in multiple items based on a range of items in the excel workbook. For my case, I would just like a static list with "Acceptable" or "Ignore" and I do not know how to add the 2 options. –  Nov 09 '22 at 08:40
  • @user692942 so in order to place the items in the drop down list, I will need to place them somewhere on the worksheet? –  Nov 09 '22 at 08:49
  • If you just want a static list, you should be able to specify the values you want in the formula as a delimited list. Try removing `"test1"` and replacing it with `"Acceptable,Ignore"` that way no cell range is required. – user692942 Nov 09 '22 at 08:58
  • 1
    Relevant - [How to create a Named Constant Array for Use in Data Validation?](https://superuser.com/a/1045145) – user692942 Nov 09 '22 at 09:27

0 Answers0