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

try this

For i = 1 To 4
ActiveSheet.OLEObjects("Listbox" & i). _
Object.AddItem "List" & i
Next i

"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

Joel;
In my 4 rangenames (K2:K5) List1, List2, List3, List4 I entered
111,222,333,444
I am trying to populate the 4 sheet listboxes with the numbers (RangeName
current
values) by using:

For i = 1 To 4
ActiveSheet.OLEObjects("Listbox" & i). _
Object.AddItem Range("List" & i.Value) << am getting R/T 424 Object
required
Next i

Can you assist me?

Tks, Jim
"Joel" wrote:

try this

For i = 1 To 4
ActiveSheet.OLEObjects("Listbox" & i). _
Object.AddItem "List" & i
Next i

"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

Why did you put i.value?
i is the counter in the for loop and doesn't have a value parameter.

"Jim May" wrote:

Joel;
In my 4 rangenames (K2:K5) List1, List2, List3, List4 I entered
111,222,333,444
I am trying to populate the 4 sheet listboxes with the numbers (RangeName
current
values) by using:

For i = 1 To 4
ActiveSheet.OLEObjects("Listbox" & i). _
Object.AddItem Range("List" & i.Value) << am getting R/T 424 Object
required
Next i

Can you assist me?

Tks, Jim
"Joel" wrote:

try this

For i = 1 To 4
ActiveSheet.OLEObjects("Listbox" & i). _
Object.AddItem "List" & i
Next i

"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

try the code below. Becuase your list boxes are 1 to 4 and the rows in
column K are 2 to 5 I used (i + 1).

For i = 1 To 4
ActiveSheet.OLEObjects("Listbox" & i). _
Object.AddItem Range("K" & (i + 1)).Value
Next i


"Jim May" wrote:

Joel;
In my 4 rangenames (K2:K5) List1, List2, List3, List4 I entered
111,222,333,444
I am trying to populate the 4 sheet listboxes with the numbers (RangeName
current
values) by using:

For i = 1 To 4
ActiveSheet.OLEObjects("Listbox" & i). _
Object.AddItem Range("List" & i.Value) << am getting R/T 424 Object
required
Next i

Can you assist me?

Tks, Jim
"Joel" wrote:

try this

For i = 1 To 4
ActiveSheet.OLEObjects("Listbox" & i). _
Object.AddItem "List" & i
Next i

"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

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

Joel -- Thanks (I did get a bit confused, sorry), but I was trying to use the
Range Names in the Macro -- where your EXCELLANT Suggestion by-passes the
Range names going straight to the Cell References. Can the Range names be
used in the macro? If so how?

"Joel" wrote:

try the code below. Becuase your list boxes are 1 to 4 and the rows in
column K are 2 to 5 I used (i + 1).

For i = 1 To 4
ActiveSheet.OLEObjects("Listbox" & i). _
Object.AddItem Range("K" & (i + 1)).Value
Next i


"Jim May" wrote:

Joel;
In my 4 rangenames (K2:K5) List1, List2, List3, List4 I entered
111,222,333,444
I am trying to populate the 4 sheet listboxes with the numbers (RangeName
current
values) by using:

For i = 1 To 4
ActiveSheet.OLEObjects("Listbox" & i). _
Object.AddItem Range("List" & i.Value) << am getting R/T 424 Object
required
Next i

Can you assist me?

Tks, Jim
"Joel" wrote:

try this

For i = 1 To 4
ActiveSheet.OLEObjects("Listbox" & i). _
Object.AddItem "List" & i
Next i

"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:
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

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

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

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

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