April 29, 2003 at 3:09 pm
I'm working on a DTS in which we automate the creation of an Excel Spreadsheet. The spreadsheet is used as a checksheet. I can place and format all my values, but can't create the validation.
I've used Excel to build a macro and tried to modify that code.
---Excel Code---
With Selection.validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:="=$A$1:$A$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Selection.AutoFill Destination:=Range("D7:F9"), Type:=xlFillDefault
Range("D7:F9").Select
The Selection reference doesn't work so I add my object reference in front of each line.
---VBScript Code---
wkbnew.worksheets(1).cells.item(rownum,7).Validation.Delete
wkbnew.worksheets(1).cells.item(rownum,7).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:="=$A$1:$A$4"
wkbnew.worksheets(1).cells.item(rownum,7).Validation.IgnoreBlank = True
wkbnew.worksheets(1).cells.item(rownum,7).Validation.InCellDropdown = True
wkbnew.worksheets(1).cells.item(rownum,7).Validation.InputTitle = ""
wkbnew.worksheets(1).cells.item(rownum,7).Validation.ErrorTitle = ""
wkbnew.worksheets(1).cells.item(rownum,7).Validation.InputMessage = ""
wkbnew.worksheets(1).cells.item(rownum,7).Validation.ErrorMessage = ""
wkbnew.worksheets(1).cells.item(rownum,7).Validation.ShowInput = True
wkbnew.worksheets(1).cells.item(rownum,7).Validation.ShowError = True
Peace Out!
Regards,
Matt
April 29, 2003 at 3:11 pm
and to finish the original post.
I receive a syntax error on the 2nd line of the vbscript code.
I've played with it for hours today and am looking for a bit of help on what the syntax error would be or how else I can format this part.
Much Thanks,
Matt
Peace Out!
Regards,
Matt
April 30, 2003 at 9:09 am
Much thanks to Chip Pearson in the microsoft.public.scripting.vbscript news group for posting the links to http://www.cpearson.com/zips/xlconsts.zip and
http://www.cpearson.com/zips/xlobjects.zip
The numeric translations for my variables was containted within.
The correct line should read as follows:
wkbnew.worksheets(1).range(rowcol).validation.add 3, 1, 1, "=$A$1:$A$4"
Peace Out!
Regards,
Matt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply