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