On Thursday, August 15, 2013 10:12:21 PM UTC-5, isabelle wrote:
hi, Le 2013-08-15 10:09, isabelle a écrit : Le 2013-08-15 09:15,
a écrit : you help me to show you can programmatically
select a item for cell A1? eg select the last item in data validation (réf.
is :name range, range address or text list) Sub
Select_Item_Data_Validation() Dim x As Integer On Error Resume Next x =
UBound(Split(Sheets("Sheet1").Range("A1").Validati on.Formula1, ";")) x =
Range(Sheets("Sheet1").Range("A1").Validation.Form ula1).Count Err.Clear If
Not IsError(Application.Find(";",
Sheets("Sheet1").Range("A1").Validation.Formula1)) Then 'text list
Sheets("Sheet1").Range("A1") =
Split(Sheets("Sheet1").Range("A1").Validation.Form ula1, ";")(x - 1) Else
Err.Clear 'name range or range address Sheets("Sheet1").Range("A1") =
Range(Sheets("Sheet1").Range("A1").Validation.Form ula1)(x) End If End Sub
isabelle

Hi Isabelle,

Wow, you are very productive. Again, I tried your code with my excel file
and received the same error message. It worked with your file well. I have
both VS2012 and 2010. abd can use both VB and C#. I reied with VB. I am not
very familiar with VBA. but can convert your VB6 code to VB.net. I don't find
a way that I can attach a file in this forum. Otherwise, I would include my
excel file, which may be save you some time.

Really appreciate your help.

Hugh

While I do like the Validation.Formula1(ListIndex) approach, it's not
supported in all versions of Excel as coded. This is why I posted a
generic reusable function that works for all versions (to date). It
uses an array approach and so you can use the list item's position in
the array in the same manner...

[A1] = vDVList(3, 1)

...to put the 3rd item in the list into A1.

--
Garry

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