本文介绍了如何使用vbs在excel中添加下拉列表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试使用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 setWhat am i doing wrong?

推荐答案

您需要替换 vba xlValidateList 的固有常数及其直接的 vbs 数值等效项(可以通过VBA帮助获得).这有效:

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中添加下拉列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-28 05:46
查看更多