Loop through listboxes

[Molden]

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

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

 

[Andrew Poulsom]

Can't you just be explicit?

Sheet1.ListBox1.Selected(n) = True
Sheet1.ListBox2.Selected(n) = True

 

[Molden]

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]

so something like

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]

Does this work for you?

    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]

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]

Like this?

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]

doh. Thanks Andrew should have spotted that.

The code works great thanks for your help