Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Runtime error on Validation object

Status
Not open for further replies.

busy_bee

Programmer
Oct 9, 2020
14
0
0
US
I have the following VBScript code.

Dim xlapp ' as excel object
Dim WSx, WSy ' as excel worksheet
Dim x, y ' as workbook
Dim fso
Dim list1
Set xlapp = CreateObject("Excel.Application")

Set fso = CreateObject("Scripting.FileSystemObject")
Dim fullpath
fullpath = fso.GetParentFolderName(WScript.ScriptFullName)

Set x = xlapp.Workbooks.Open(fullPath & "\File1.xlsx")
Set y = xlapp.Workbooks.Open(fullPath & "\File2.xlsm")

Set WSx = x.Worksheets("Sheet1")
Set WSy = y.Worksheets("Sheet1")

WSy.Cells.Clear
WSx.UsedRange.Copy WSy.Range("A1")
Set WSx = nothing
x.Close

WSy.Range("F1").Value="Yes/No"
With WSy.Range("F2").Validation
.Add xlValidateList, xlValidAlertStop, , "Option1,Option2"
.ErrorTitle = "Not a Valid Selection"
.ErrorMessage = "Please make sure you spelled the item correctly or select the item from the dropdowm menu."
.IgnoreBlank = True
.InCellDropdown = True
End With


Set WSy = nothing

y.Save
y.close
xlapp.quit

When I try to execute this code, it gives the following error on this line:
.Add xlValidateList, xlValidAlertStop, , "Option1,Option2"
microsoft vbscript runtime error unknown runtime error

Any suggestions on how to resolve this?
 
Hi,

You need to use the numeric values for xlValidateList & xlValidAlertStop, not the Excel constants, for which you need the Excel run-time library.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
My code now executes correctly. Thank you.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top