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

A better error handler, perhaps!

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

To
On Error GoTo noDV
sDVFormula1 = CellRef.Validation.Formula1


And a better code caller handler, perhaps...

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

On Error GoTo noDV
If CellAddr = "" Then _
sDVFormula1 = CellRef.Validation.Formula1 _
Else sDVFormula1 = Range(CellAddr).Validation.Formula1

...where line has been commented out so it doesn't recalc for each using
cell.

I don't have my v2010 installed yet so if Claus could test the other
sheet DV ref part and confirm results I'd appreciate it very much!

--
Garry

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

thank you garry, i tried your function and it works fine for the 3 cases
possible of a validation source. i changed a little your macro in the
case if cell is not a data validation

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

If IsError(CellRef.Validation.Formula1) Then
Err.Clear
GetDV_ListItems = "No Data Validation"
Exit Function
End If

sDVFormula1 = CellRef.Validation.Formula1

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

GetDV_ListItems = sDVFormula1

End Function

isabelle

i tried your function and it works fine for the 3 cases possible of a
validation source

Thanks for the feedback, Isabelle! I assume then that you tried it in
v2010?

Yes, I did find the error handling needed a change. I elected to
redirect on error so user gets informed what's going on. I like your
version, though!<g Here's my code-enabled version revised as per your
changes...

Function GetDV_ListItems$(CellRef As Range, Optional CellAddr$)
Dim sDVFormula1$, vDVList, vTmp, n&, rng As Range
' Application.Volatile

If CellAddr = "" Then Set rng = CellRef _
Else Set rng = Range(CellAddr)

On Error Resume Next
If IsError(rng.Validation.Formula1) Then
GetDV_ListItems = "No Data Validation"
Err.Clear: Exit Function
End If

sDVFormula1 = rng.Validation.Formula1
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

GetDV_ListItems = sDVFormula1
End Function

--
Garry

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

yes Garry, i have tried on v2010, thank you again for this code it's
very appreciated

isabelle

Le 2013-08-15 20:47, GS a écrit :
i tried your function and it works fine for the 3 cases possible of a
validation source

Thanks for the feedback, Isabelle! I assume then that you tried it in
v2010?

Yes, I did find the error handling needed a change. I elected to
redirect on error so user gets informed what's going on. I like your
version, though!<g Here's my code-enabled version revised as per your
changes...

Function GetDV_ListItems$(CellRef As Range, Optional CellAddr$)
Dim sDVFormula1$, vDVList, vTmp, n&, rng As Range
' Application.Volatile

If CellAddr = "" Then Set rng = CellRef _
Else Set rng = Range(CellAddr)

On Error Resume Next
If IsError(rng.Validation.Formula1) Then
GetDV_ListItems = "No Data Validation"
Err.Clear: Exit Function
End If

sDVFormula1 = rng.Validation.Formula1
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

GetDV_ListItems = sDVFormula1
End Function

That's great!
You're welcome to freely use/modify the code however you like. (I don't
need credit mention either)

--
Garry

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