Akhil_Bansal20
2007-08-29 11:32:01 UTC
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
Any suggestion / pointers / code will be highly appreciated.
Thanks
Akhil Bansal
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
Any suggestion / pointers / code will be highly appreciated.
Thanks
Akhil Bansal