Discussion:
Adding Excel data validation
(too old to reply)
Akhil_Bansal20
2007-08-29 11:32:01 UTC
Permalink
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
urkec
2007-08-29 14:56:02 UTC
Permalink
Post by Akhil_Bansal20
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
Akhil_Bansal20
2007-08-30 04:28:00 UTC
Permalink
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.
Post by urkec
Post by Akhil_Bansal20
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
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
Gene Magerr
2010-09-10 00:46:04 UTC
Permalink
I think you left a part out at the end

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
Post by Akhil_Bansal20
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
Post by urkec
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
Post by Akhil_Bansal20
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.
Submitted via EggHeadCafe - Software Developer Portal of Choice
Scrolling in WPF Toolkit?s Column Chart
http://www.eggheadcafe.com/tutorials/aspnet/0939d60c-8e17-4a27-b898-1fc772d2d6f6/scrolling-in-wpf-toolkits-column-chart.aspx
Gene Magerr
2010-09-10 00:48:13 UTC
Permalink
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

Submitted via EggHeadCafe - Software Developer Portal of Choice
Simple .NET HEX PixelColor Utility
http://www.eggheadcafe.com/tutorials/aspnet/5617a491-963d-4510-b8f1-1863ddf52bc1/simple-net-hex-pixelcolor-utility.aspx
Loading...