# Loop through listboxes

#### Molden

##### Active Member
Hi All,

I have 2 listboxes on sheet1 of my workbook and I was hoping their was a way to loop through them as the same code applies to them both?

My code is

Code:
``````Dim rng As Range
MyArray = Array("A2:A5", "F2:F4")

For Each x In MyArray

n = 0

For Each rng In .Range(x)

If rng.Offset(0, 1) = True Then
Sheet1.ListBox1.Selected(n) = True
End If

n = n + 1

Next``````

i then need to apply the same code to listbox2

Is there a way of just putting something like Sheet1.ListBox & x.Selected = True
where x is 1 then 2?

Thanks

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Andrew Poulsom

##### MrExcel MVP
Can't you just be explicit?

Rich (BB code):
``````Sheet1.ListBox1.Selected(n) = True
Sheet1.ListBox2.Selected(n) = True``````

#### Molden

##### Active Member
I dont think so because in my array the 1st range i.e. A2:A5 is aplicable to listbox1 and the 2nd range (F2:F4) is applicable to listbox 2

so rather then do a for statement looking at a2:a5 and then influencing listbox1 then to do pretty much the same code apart from the range changes for listbox2

I was hoping i could just do a loop on the control as well?

#### Molden

##### Active Member
so something like

Code:
``````MyArray = Array("A2:A5", "F2:F4")
cCont = (Listbox1, Listbox2)

For Each c in cCont
For Each x In MyArray

n =0
For Each rng In .Range(x)

If rng.Offset(0, 1) = True Then
Sheet1.c.Selected(n) = True
End If

n = n+1

next
next
next``````

#### Andrew Poulsom

##### MrExcel MVP
Does this work for you?

Code:
``````    Dim MyArray As Variant
Dim cCont As Variant
Dim i As Long
Dim n As Long
Dim Rng As Range
MyArray = Array("A2:A5", "F2:F4")
cCont = Array("Listbox1", "Listbox2")
For i = LBound(MyArray) To UBound(MyArray)
n = 0
For Each Rng In Range(MyArray(i))
Sheet1.OLEObjects(cCont(i)).Object.Selected(n) = Rng.Offset(0, 1) = True
n = n + 1
Next Rng
Next I``````

#### Molden

##### Active Member
it works great except that the code is going to work on workbook open so the 2 ranges (A2:A5 and F2:F4) are ranges from sheet3.

Is their anyway the sheet reference can be included as it works but only if i run it whilst on sheet3?

Thanks for your help on this

#### Andrew Poulsom

##### MrExcel MVP
Like this?

Code:
``For Each Rng In Worksheets("Sheet3").Range(MyArray(i))``

I had to remove the dot qualifier before Range in your code because there was no With ... End With construct.

#### Molden

##### Active Member
doh. Thanks Andrew should have spotted that.

The code works great thanks for your help

Replies
0
Views
184
Replies
0
Views
389
Replies
2
Views
250
Replies
0
Views
543
Replies
8
Views
517

1,191,383
Messages
5,986,307
Members
440,017
Latest member
vasanrajeswaran

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back