如何使用vbs在excel中添加下拉列表 [英] how to add a drop down list in excel using vbs

问题描述

我正在尝试使用vbs在Excel中添加下拉菜单，并且出现以下错误.

Hi I am trying to add a drop down in a excel using vbs and i am getting the below error.

vbs(18, 15) Microsoft VBScript compilation error: Syntax error

我什至录制了一个marco并从那里使用了代码，但仍然行不通.

I even recorded a marco and used the code from there still it didn't work.

第18行和第15行是带有add关键字的行.

Line 18 and col 15 is the line with the add keyword.

Range("A1").Select
    With Selection.Validation
        .Delete
        .Add (Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$Q$9:$Q$11")
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Q9:Q11有一个样本数据集我在做什么错了?

Q9:Q11 has a sample data set What am i doing wrong?

推荐答案

您需要替换 vba xlValidateList 的固有常数及其直接的

You need to replace the vba intrinsic constants of xlValidateList with their direct vbs numeric equivalent (which you can get via VBA help). This works:

Dim objExcel, objWB, objws
Set objExcel = CreateObject("excel.application")
Set objWB = objExcel.Workbooks.Add
Set objws = objWB.Sheets(1)
With objws.Range("A1").Validation
        .Add 3, 1, 1, "=$Q$9:$Q$11"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
 end With

这篇关于如何使用vbs在excel中添加下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！