Read Dropdown list from Excel column

  • Thread starter Thread starter ldiowa
  • Start date Start date
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, (e-mail address removed) a écrit : > >> >> oSheet.Range("A1").Validation.Formula1 generatedthe error. I tried >> Formula2 and received the same error. >>
Click to expand...

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
 
hi,

Le 2013-08-15 09:15, (e-mail address removed) a écrit :
My question is: where can I find my "ValList"?
Click to expand...

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

sheet_ValList =
Range(Sheets("Sheet1").Range("A1").Validation.Formula1).Parent.Name
address_ValList =
Range(Sheets("Sheet1").Range("A1").Validation.Formula1).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?
Click to expand...

'eg select the fourth item in the list

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

isabelle
 
hi, Le 2013-08-15 09:15, (e-mail address removed) 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.Formula1).Parent.Name address_ValList = Range(Sheets("Sheet1")..Range("A1").Validation.Formula1).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.Formula1)(4) End Sub isabelle
Click to expand...

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.
 
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, (e-mail address removed) a écrit :
 
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).Address
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
 
Hi,

Am Thu, 15 Aug 2013 08:16:52 -0700 (PDT) schrieb (e-mail address removed):
and received the error of "Exception from HRESULT: 0x800A03EC". It means that Formula was not found.
Click to expand...

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.
 
hi,

Le 2013-08-15 10:09, isabelle a écrit :
Le 2013-08-15 09:15, (e-mail address removed) a écrit :
Click to expand...

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").Validation.Formula1, ";"))
x = Range(Sheets("Sheet1").Range("A1").Validation.Formula1).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.Formula1, ";")(x - 1)
Else
Err.Clear
'name range or range address
Sheets("Sheet1").Range("A1") =
Range(Sheets("Sheet1").Range("A1").Validation.Formula1)(x)
End If
End Sub

isabelle
 
Hi, Am Thu, 15 Aug 2013 08:16:52 -0700 (PDT) schrieb (e-mail address removed): > 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
Click to expand...

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.
 
hi, Le 2013-08-15 10:09, isabelle a écrit : > Le 2013-08-15 09:15, (e-mail address removed) 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").Validation.Formula1, ";")) x = Range(Sheets("Sheet1").Range("A1").Validation.Formula1).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.Formula1, ";")(x - 1) Else Err.Clear 'name range or range address Sheets("Sheet1").Range("A1") = Range(Sheets("Sheet1").Range("A1").Validation.Formula1)(x) End If End Sub isabelle
Click to expand...

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 amnot 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
 
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
Click to expand...

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
 
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
 
Back
Top