"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

Hello Urkec,

That was a nice suggestion. I tried but it is not working. I even tried to
add constant in add functions also, but it is not working. Strange thing is
that it is not giving any error also.


"urkec" wrote:

> "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