Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#42
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read Dropdown list from Excel column
|
#44
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#46
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read Dropdown list from Excel column
|
#47
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#48
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to read the contents of an OLEObject (dropdown list) in excel | Excel Programming | |||
Creating a dropdown list within a column | Excel Discussion (Misc queries) | |||
Dropdown List that populates another column | Excel Worksheet Functions | |||
Dropdown List - list item endings not visible if column too narrow | Excel Discussion (Misc queries) | |||
Procedure to make a dropdown list larger so it's easier to read | Excel Discussion (Misc queries) |