Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #41   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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
  #44   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default 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.

  #45   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default 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



  #48   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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.
  #49   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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
  #50   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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




  #51   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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
  #52   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to read the contents of an OLEObject (dropdown list) in excel FurRelKT Excel Programming 0 August 3rd 10 06:01 PM
Creating a dropdown list within a column dsebiz Excel Discussion (Misc queries) 4 March 19th 08 06:56 PM
Dropdown List that populates another column HAZBONES Excel Worksheet Functions 7 December 1st 07 04:10 AM
Dropdown List - list item endings not visible if column too narrow AK9955 Excel Discussion (Misc queries) 2 April 27th 07 09:02 AM
Procedure to make a dropdown list larger so it's easier to read Jack Oziel Excel Discussion (Misc queries) 4 December 26th 05 06:49 PM


All times are GMT +1. The time now is 09:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"