| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping thorugh Listboxs to populate with name range
Which listboxes did you use?
The Controls Listbox What are the names of the listboxes? Listbox1<<,from Properties Dialog box "(Name)" Listbox2 Listbox3 Listbox4 What are the names of the ranges? List1 -- Refers to: =Sheet1!$K$2 List2 -- Refers to: =Sheet1!$K$3 List3 -- Refers to: =Sheet1!$K$4 List4 -- Refers to: =Sheet1!$K$5 Thanks for helping... "Dave Peterson" wrote: Which listboxes did you use? What are the names of the listboxes? What are the names of the ranges? Jim May wrote: Dave: I used you code (modified to reflect my rangename assignment is to Sheet1), But I'm currently getting: R/T 381 - cannot set the list property - Invalid Property array index. Any insight for me? Thaks, Jim "Dave Peterson" wrote: Are these listboxes from the Control toolbox toolbar? Did you name them nicely (Listbox1, listbox2, listbox3, listbox4)? If yes to both... I put the named ranges on Sheet2 and the listboxes on sheet1: Dim iCtr As Long For iCtr = 1 To 4 Worksheets("sheet1").OLEObjects("Listbox" & iCtr).Object.List _ = Worksheets("sheet2").Range("List" & iCtr).Value Next iCtr If these are listboxes from the Forms toolbar, the code changes: Dim iCtr As Long For iCtr = 1 To 4 Worksheets("sheet1").ListBoxes("List box " & iCtr).List _ = Worksheets("sheet2").Range("List" & iCtr).Value Next iCtr This expects that these listboxes are named nicely, too: List Box 1, List Box 2, List Box 3, List Box 4 CmK wrote: Hi I have four listboxs on one sheet how do i loop through all the listboxs on one Worksheet then populate them with named range Named range are List1,List2,List3 and List4 So i need List1 to be populated in the first Listbox then List2 to be populate into the second Listbox etc.... Example sub poplistboxx() Dim listbox as object Dim i as integer For each listbox in activesheet ?????? Thanks in advance -- Dave Peterson -- Dave Peterson |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorugh Listboxs to populate with name range
You want a listbox with a single option????
If you make the ranges refer to more than one cell, then it should work ok. But the listbox's list is looking for an array. And the .value of a single cell won't qualify. But if you really wanted (or weren't sure what that name pointed to): Dim myArr As Variant Dim iCtr As Long For iCtr = 1 To 4 With Worksheets("sheet1").Range("List" & iCtr) If .Cells.Count = 1 Then myArr = Array(.Value) Else myArr = .Value End If End With Worksheets("sheet1").OLEObjects("Listbox" & iCtr).Object.List = myArr Next iCtr Just a warning <bg, if list# consists of multiple areas, then this will break, too! Jim May wrote: Which listboxes did you use? The Controls Listbox What are the names of the listboxes? Listbox1<<,from Properties Dialog box "(Name)" Listbox2 Listbox3 Listbox4 What are the names of the ranges? List1 -- Refers to: =Sheet1!$K$2 List2 -- Refers to: =Sheet1!$K$3 List3 -- Refers to: =Sheet1!$K$4 List4 -- Refers to: =Sheet1!$K$5 Thanks for helping... "Dave Peterson" wrote: Which listboxes did you use? What are the names of the listboxes? What are the names of the ranges? Jim May wrote: Dave: I used you code (modified to reflect my rangename assignment is to Sheet1), But I'm currently getting: R/T 381 - cannot set the list property - Invalid Property array index. Any insight for me? Thaks, Jim "Dave Peterson" wrote: Are these listboxes from the Control toolbox toolbar? Did you name them nicely (Listbox1, listbox2, listbox3, listbox4)? If yes to both... I put the named ranges on Sheet2 and the listboxes on sheet1: Dim iCtr As Long For iCtr = 1 To 4 Worksheets("sheet1").OLEObjects("Listbox" & iCtr).Object.List _ = Worksheets("sheet2").Range("List" & iCtr).Value Next iCtr If these are listboxes from the Forms toolbar, the code changes: Dim iCtr As Long For iCtr = 1 To 4 Worksheets("sheet1").ListBoxes("List box " & iCtr).List _ = Worksheets("sheet2").Range("List" & iCtr).Value Next iCtr This expects that these listboxes are named nicely, too: List Box 1, List Box 2, List Box 3, List Box 4 CmK wrote: Hi I have four listboxs on one sheet how do i loop through all the listboxs on one Worksheet then populate them with named range Named range are List1,List2,List3 and List4 So i need List1 to be populated in the first Listbox then List2 to be populate into the second Listbox etc.... Example sub poplistboxx() Dim listbox as object Dim i as integer For each listbox in activesheet ?????? Thanks in advance -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorugh Listboxs to populate with name range
Thanks VERY MUCH Dave
"Dave Peterson" wrote: You want a listbox with a single option???? If you make the ranges refer to more than one cell, then it should work ok. But the listbox's list is looking for an array. And the .value of a single cell won't qualify. But if you really wanted (or weren't sure what that name pointed to): Dim myArr As Variant Dim iCtr As Long For iCtr = 1 To 4 With Worksheets("sheet1").Range("List" & iCtr) If .Cells.Count = 1 Then myArr = Array(.Value) Else myArr = .Value End If End With Worksheets("sheet1").OLEObjects("Listbox" & iCtr).Object.List = myArr Next iCtr Just a warning <bg, if list# consists of multiple areas, then this will break, too! Jim May wrote: Which listboxes did you use? The Controls Listbox What are the names of the listboxes? Listbox1<<,from Properties Dialog box "(Name)" Listbox2 Listbox3 Listbox4 What are the names of the ranges? List1 -- Refers to: =Sheet1!$K$2 List2 -- Refers to: =Sheet1!$K$3 List3 -- Refers to: =Sheet1!$K$4 List4 -- Refers to: =Sheet1!$K$5 Thanks for helping... "Dave Peterson" wrote: Which listboxes did you use? What are the names of the listboxes? What are the names of the ranges? Jim May wrote: Dave: I used you code (modified to reflect my rangename assignment is to Sheet1), But I'm currently getting: R/T 381 - cannot set the list property - Invalid Property array index. Any insight for me? Thaks, Jim "Dave Peterson" wrote: Are these listboxes from the Control toolbox toolbar? Did you name them nicely (Listbox1, listbox2, listbox3, listbox4)? If yes to both... I put the named ranges on Sheet2 and the listboxes on sheet1: Dim iCtr As Long For iCtr = 1 To 4 Worksheets("sheet1").OLEObjects("Listbox" & iCtr).Object.List _ = Worksheets("sheet2").Range("List" & iCtr).Value Next iCtr If these are listboxes from the Forms toolbar, the code changes: Dim iCtr As Long For iCtr = 1 To 4 Worksheets("sheet1").ListBoxes("List box " & iCtr).List _ = Worksheets("sheet2").Range("List" & iCtr).Value Next iCtr This expects that these listboxes are named nicely, too: List Box 1, List Box 2, List Box 3, List Box 4 CmK wrote: Hi I have four listboxs on one sheet how do i loop through all the listboxs on one Worksheet then populate them with named range Named range are List1,List2,List3 and List4 So i need List1 to be populated in the first Listbox then List2 to be populate into the second Listbox etc.... Example sub poplistboxx() Dim listbox as object Dim i as integer For each listbox in activesheet ?????? Thanks in advance -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| routing email thorugh excel | Excel Discussion (Misc queries) | |||
| which text be selects in VBA listboxs | Excel Programming | |||
| How do I modify charts thorugh VB | Excel Programming | |||
| Looping through data and Populate template | Excel Programming | |||
| listboxs and combo boxes | Excel Programming | |||
Hybrid Mode
