On Friday, August 16, 2013 9:55:20 AM UTC-5, GS wrote:
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

Hi GS,

I did try your code and it worked with the File that Isabelle provided but did not work with my excel file. It stopped at

InStr(.Formula1, "=")

the error was the same as before because there is no Formula. I am sorry for late reply. I messed you up with Isabelle and replied to Isabelle.

Thanks very much for your help.

Hugh