Hi there,

I have a excel file that has dropdown list in a cell. I want to programatically set the value of the cell using following code and did not work.

oSheet.Range["A1"].Value = "A1";

The same code works for a regular cell. Looks like that the dropdown listitems were from another sheet.

Thanks very much for your help.

Hugh

Hi there,

I have a excel file that has dropdown list in a cell. I want to
programatically set the value of the cell using following code and did not
work.

oSheet.Range["A1"].Value = "A1";

The same code works for a regular cell. Looks like that the dropdown
listitems were from another sheet.

Thanks very much for your help.

Hugh

As I mentioned in your other thread.., the value you're trying to enter
in a DV cell must be in the DV List. Otherwise, it doesn't work! Try
assigning one of the list items!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Wednesday, August 14, 2013 10:05:38 AM UTC-5, GS wrote:
Hi there, I have a excel file that has dropdown list in a cell. I want to programatically set the value of the cell using following code and did not work. oSheet.Range["A1"].Value = "A1"; The same code works for a regular cell. Looks like that the dropdown listitems were from another sheet. Thanks very much for your help. Hugh As I mentioned in your other thread.., the value you're trying to enter in a DV cell must be in the DV List. Otherwise, it doesn't work! Try assigning one of the list items! -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion


First, thanks very much for your help again. Yes, the value entered is one of the DV list items. But it did not work. I must miss something here. Please elaborate what you said, Gary. There is not like string.contains or string.match function that can be used.

On Wednesday, August 14, 2013 10:05:38 AM UTC-5, GS wrote:
Hi there, I have a excel file that has dropdown list in a cell. I want
to programatically set the value of the cell using following code and did
not work. oSheet.Range["A1"].Value = "A1"; The same code works
for a regular cell. Looks like that the dropdown listitems were from
another sheet. Thanks very much for your help. Hugh As I mentioned
in your other thread.., the value you're trying to enter in a DV cell must
be in the DV List. Otherwise, it doesn't work! Try assigning one of the
list items! -- Garry Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


First, thanks very much for your help again. Yes, the value entered is one
of the DV list items. But it did not work. I must miss something here.
Please elaborate what you said, Gary. There is not like string.contains or
string.match function that can be used.

Sorry, Hugh, but I don't speak C#. I'm sure, though, that every
function available in VB[A] (and much more) is available in the .Net
languages in some way. Sorry I can't be of help with your syntax. My
hope is that you could convert my sample code...

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Wednesday, August 14, 2013 10:50:35 AM UTC-5, GS wrote:
On Wednesday, August 14, 2013 10:05:38 AM UTC-5, GS wrote: Hi there, I have a excel file that has dropdown list in a cell. I want to programatically set the value of the cell using following code and did not work. oSheet.Range["A1"].Value = "A1"; The same code works for a regular cell. Looks like that the dropdown listitems were from another sheet. Thanks very much for your help. Hugh As I mentioned in your other thread.., the value you're trying to enter in a DV cell must be in the DV List. Otherwise, it doesn't work! Try assigning one of the list items! -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion First, thanks very much for your help again. Yes, the value entered is one of the DV list items. But it did not work. I must miss something here. Please elaborate what you said, Gary. There is not like string.contains or string.match function that can be used. Sorry, Hugh, but I don't speak C#. I'm sure, though, that every function available in VB[A] (and much more) is available in the .Net languages in some way. Sorry I can't be of help with your syntax. My hope is that you could convert my sample code... -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion

Translation to C# is not a problem. As a matter of fact, oSheet.Range("A1").Validation.Formula1, or oSheet.Cells(1,1).Validation.Formula1 returned a error. The eoor is generic and means that Excel did not find Formula1. However, Formula1 is available under Validation. I am not sure if there is DV List name and I am new to Excel stuff. But I did find that Lititems are from another sheet.

Here's a reusable function that demos how to process
Validation.Formula1 results for the different scenarios. Note that it
can be run from a worksheet cell or called from a sub...

Function GetDV_ListItems(CellRef As Range) As String
Dim sDVFormula1$, vDVList, vTmp, n&
Application.Volatile

sDVFormula1 = CellRef.Validation.Formula1
If sDVFormula1 = "" Then GoTo noDV

If Left(sDVFormula1, 1) = "=" Then '//it's a range ref
sDVFormula1 = Mid(sDVFormula1, 2)
n = InStr(1, sDVFormula1, "!")
If n 0 Then 'it refs another sheet
vTmp = Split(sDVFormula1, "!")
vDVList = Sheets(Replace(vTmp(0), "'", "")).Range(vTmp(1))
Else '//it's a local ref
vDVList = Range(sDVFormula1)
End If 'iPos 0

If UBound(vDVList) = LBound(vDVList) Then 'it's a row list
For n = LBound(vDVList) To UBound(vDVList, 2)
vTmp = vTmp & "," & vDVList(1, n)
Next 'n
Else 'it's a col list
For n = LBound(vDVList) To UBound(vDVList)
vTmp = vTmp & "," & vDVList(n, 1)
Next 'n
End If
sDVFormula1 = Mid(vTmp, 2)
End If

NormalExit:
GetDV_ListItems = sDVFormula1
Exit Function

noDV:
MsgBox "The cell reference you entered has no Data Validation",
vbExclamation
End Function

Note that it uses Application.Volatile so it auto updates to reflect
changes in the DV list's source range. Also, it strips the leading '='
operator from range refs even though this is not necessary in Excel
with VBA. I stripped it here merely to show how other apps using
automation can get a 'clean' range name. In the case where Formula1
includes another sheetname it prepends a fully qualified ref to its
Range object. You will have to do same for all cases using automation.
This means your 'oSheet' variable must ref
'appXL.Workbooks("SoAndSo.xls").Sheets("SuchAndSuc h").Cells(n,
n).Validation.Formula1 so you have a fully qualified path to the
property value you're after.

I stand corrected in my assertion that code can't put non-DVList values
in a cell with DV! This is not true as I just tested your task and I
was able to enter any value I wanted into the cells with DV. That means
the restriction is only via the UI.

I like Isabelle's approach of using the Validation.Formula1(ListIndex)
because it makes searching the list for a specific entry via looping a
very easy implementation if you need to grab a specific value in code
based on user input criteria.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion