Read Dropdown list from Excel column
 

On Wednesday, August 14, 2013 5:06:55 PM UTC-5, isabelle wrote:
if the source range was named and that name was used to create the validation this code: Range("A1").Validation.Formula1 returns the name of the source then you can use it like this: Range(Range("A1").Validation.Formula1).Copy Range("A1").Offset(0, 3) isabelle Le 2013-08-14 16:57, isabelle a écrit : hi Hugh, i made a model using a name for the validation http://cjoint.com/?CHow1NBP9ko isabelle Le 2013-08-14 15:44, a écrit : oSheet.Range("A1").Validation.Formula1 generated the error. I tried Formula2 and received the same error.

Hi Isabelle,

It looks like that the dropdown List on my file is not named.
I ran your model and irt worked well. and I did see "ValList". I ran my simple code of

string s = oSheet.Range["A1"].Balidation.Formula1;

agaist your file and received "ValList", and received the error message of Formula1 not found. I know that you already helped me a lot, any more suggestions?

Hugh

Read Dropdown list from Excel column
 

hi,

Le 2013-08-15 09:15, a écrit :

My question is: where can I find my "ValList"?

Sub test1()
Dim sheet_ValList As String, address_ValList As String

sheet_ValList =
Range(Sheets("Sheet1").Range("A1").Validation.Form ula1).Parent.Name
address_ValList =
Range(Sheets("Sheet1").Range("A1").Validation.Form ula1).Address

MsgBox sheet_ValList & "!" & address_ValList

If Sheets(sheet_ValList).Visible Then
Application.Goto Range(sheet_ValList & "!" & address_ValList)
Else
Sheets(sheet_ValList).Visible = True
Application.Goto Range(sheet_ValList & "!" & address_ValList)
End If
End Sub

you help me to show you can programmatically select a item for cell A1?

'eg select the fourth item in the list

Sub test2()
Sheets("Sheet1").Range("A1") =
Range(Sheets("Sheet1").Range("A1").Validation.Form ula1)(4)
End Sub

isabelle

Read Dropdown list from Excel column
 

On Thursday, August 15, 2013 9:09:33 AM UTC-5, isabelle wrote:
hi, Le 2013-08-15 09:15, a écrit : My question is: where can I find my "ValList"? Sub test1() Dim sheet_ValList As String, address_ValList As String sheet_ValList = Range(Sheets("Sheet1").Range("A1").Validation.Form ula1).Parent.Name address_ValList = Range(Sheets("Sheet1")..Range("A1").Validation.For mula1).Address MsgBox sheet_ValList & "!" & address_ValList If Sheets(sheet_ValList).Visible Then Application.Goto Range(sheet_ValList & "!" & address_ValList) Else Sheets(sheet_ValList).Visible = True Application.Goto Range(sheet_ValList & "!" & address_ValList) End If End Sub you help me to show you can programmatically select a item for cell A1? 'eg select the fourth item in the list Sub test2() Sheets("Sheet1").Range("A1") = Range(Sheets("Sheet1").Range("A1").Validation.Form ula1)(4) End Sub isabelle

Hi Isabelle,

Thanks very much for your help again. As I replied to your another post. It seems that dropdown list in my excel file was not named. I ran the code..

string s = oSheet.Range["B11"].Validation.Formula1;

and received the error of "Exception from HRESULT: 0x800A03EC". It means that Formula was not found.

Read Dropdown list from Excel column
 

hi,

try with the real name of the sheet,
and make sure there is a validation list in cell B11
what is the MsgBox result on the next command line?

MsgBox Sheets("Sheet1").Range("B11").Validation.Formula1

isabelle

Le 2013-08-15 11:16, a écrit :

Hi Isabelle,

Thanks very much for your help again. As I replied to your another post. It seems that dropdown list in my excel file was not named. I ran the code.

string s = oSheet.Range["B11"].Validation.Formula1;

and received the error of "Exception from HRESULT: 0x800A03EC". It means that Formula was not found.

Read Dropdown list from Excel column
 

or

what is the MsgBox result for the next code?

Sub test5()
Dim x As String
On Error Resume Next
With ActiveSheet
x = .Cells.SpecialCells(xlCellTypeAllValidation)(1).Ad dress
If Err.Number < 0 Then
Err.Clear
MsgBox "There is no validation list on this sheet"
Else
MsgBox .Range(x).Validation.Formula1
End If
End With
End Sub

isabelle

Read Dropdown list from Excel column
 

Hi,

Am Thu, 15 Aug 2013 08:16:52 -0700 (PDT) schrieb :

and received the error of "Exception from HRESULT: 0x800A03EC". It means that Formula was not found.

try:

Sub ValidationItems()
Dim strSh As String
Dim strRng As String
Dim myStr As String
Dim myArr As Variant
Dim i As Integer
Dim rngC As Range

With Sheets("Sheet1").Range("A1").Validation
If InStr(.Formula1, "=") = 0 Then
myStr = .Formula1
myArr = Split(myStr, ";")
End If
If InStr(.Formula1, "!") 0 Then
strSh = Mid(.Formula1, 2, InStr(.Formula1, "!") - 1)
strRng = Mid(.Formula1, InStr(.Formula1, "!") + 1, 99)
ElseIf InStr(.Formula1, "=") 0 Then
strRng = Replace(.Formula1, "=", "")
End If

If strSh < "" Then
ReDim myArr(Sheets(strSh).Range(strRng).Cells.Count - 1)
For Each rngC In Sheets(strSh).Range(strRng)
myArr(i) = rngC
i = i + 1
Next
ElseIf strSh = "" And strRng < "" Then
ReDim myArr(Range(strRng).Cells.Count - 1)
For Each rngC In Range(strRng)
myArr(i) = rngC
i = i + 1
Next
End If
End With
Range("L1").Resize(UBound(myArr) + 1) = _
WorksheetFunction.Transpose(myArr)
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Read Dropdown list from Excel column
 

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

Read Dropdown list from Excel column
 

On Thursday, August 15, 2013 11:50:32 AM UTC-5, Claus Busch wrote:
Hi, Am Thu, 15 Aug 2013 08:16:52 -0700 (PDT) schrieb : and received the error of "Exception from HRESULT: 0x800A03EC". It means that Formula was not found. try: Sub ValidationItems() Dim strSh As String Dim strRng As String Dim myStr As String Dim myArr As Variant Dim i As Integer Dim rngC As Range With Sheets("Sheet1").Range("A1").Validation If InStr(.Formula1, "=") = 0 Then myStr = .Formula1 myArr = Split(myStr, ";") End If If InStr(.Formula1, "!") 0 Then strSh = Mid(.Formula1, 2, InStr(.Formula1, "!") - 1) strRng = Mid(.Formula1, InStr(.Formula1, "!") + 1, 99) ElseIf InStr(.Formula1, "=") 0 Then strRng = Replace(.Formula1, "=", "") End If If strSh < "" Then ReDim myArr(Sheets(strSh).Range(strRng).Cells.Count - 1) For Each rngC In Sheets(strSh).Range(strRng) myArr(i) = rngC i = i + 1 Next ElseIf strSh = "" And strRng < "" Then ReDim myArr(Range(strRng).Cells.Count - 1) For Each rngC In Range(strRng) myArr(i) = rngC i = i + 1 Next End If End With Range("L1").Resize(UBound(myArr) + 1) = _ WorksheetFunction.Transpose(myArr) End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

Hi,

I tried your code (I use VB2010) on Isabelle's excel file, it worked fine. But not on my excel file. Again the error message pops up: Exception from HRESULT: 0x800A03EC.

Thanks very much for your help.

Read Dropdown list from Excel column
 

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

Read Dropdown list from Excel column
 

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

Read Dropdown list from Excel column
 

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

Read Dropdown list from Excel column
 

Isabelle and I have collaborated somewhat. You'll find my final code
posted in your other thread as a generic reusable function that handles
if a cell has no Validation.Formula1 near the beginning so it cancels
before doing anything.

--
Garry

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