"Akhil_Bansal20" wrote:

> Hi
>
> I need to add excel data validation (list) to a cell so that when user
> clicks on the cell, he gets a drop down listing all possible values. I can do
> this using VB.net but when i am trying to do it in VB script it is not
> working. The code that I am using is as follows.
>
> Dim objXls
> Dim wrkBook
> Dim wrkSheet
> Dim tmp
> Dim RowRange
> Dim Yes
> Dim No
>
> set objXls = CreateObject("Excel.Application")
> objXls.Visible = True
>
> set wrkBook = objXls.Workbooks.add() ' Open the Workbook
> set wrkSheet = wrkBook.Worksheets(1) ' Get Reference to
> WorkBook
> wrkSheet.Activate()
>
> wrkSheet.Range("A65535") = "yes"
> wrkSheet.Range("A65536") = "No"
>
>
> set tmp = wrkSheet.Range("A65535:A65536")
> set RowRange = wrkSheet.Range("A10")
> RowRange.value="Akhil"
> RowRange.Interior.ColorIndex = 5
> 'msgbox(Excel.XlDVType.xlValidateList)
>
>
> With RowRange.Validation
> .Add(Excel.XlDVType.xlValidateList,
> Excel.XlDVAlertStyle.xlValidAlertStop,
> Excel.XlFormatConditionOperator.xlBetween, "=$A$65535:$A$65536")
> .InCellDropdown = True
> End With
>

In VBScript you need to define Excel costants:


const xlValidAlertStop = 1
const xlBetween = 1
const xlValidateList = 3

set objXls = CreateObject("Excel.Application")
objXls.Visible = True

set wrkBook = objXls.Workbooks.add()
set wrkSheet = wrkBook.Worksheets(1)
wrkSheet.Activate()

wrkSheet.Range("A65535") = "Yes"
wrkSheet.Range("A65536") = "No"

set tmp = wrkSheet.Range("A65535:A65536")
set RowRange = wrkSheet.Range("A1")
RowRange.value="Akhil"
'RowRange.Interior.ColorIndex = 5

With RowRange.Validation
..Add _
xlValidateList, _
xlValidAlertStop, _
xlBetween, _
"=$A$65535:$A$65536"
..InCellDropdown = True
End With

--
urkec