Naming a listbox as a string

[johan.mcconnell]

I need to label a listbox as a string (or whatever works) so I can
access the properties in a loop. Hard to explain but here's basically
what I need done:

Original Code:
Private Sub ListBox1_Change()
ActiveSheet.Range("B2:Z2").Clear
Dim i As Integer, li As Integer
For li = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(li) Then
ActiveSheet.Range("B2").Offset(i, 0) = ListBox1.List(li)
i = i + 1
End If
Next
End Sub

Desired Code:
Private Sub ListBox1_Change()
ActiveSheet.Range("B2:Z2").Clear
Dim i As Integer, li As Integer, listboxname As String
For li = 0 To listboxname.ListCount - 1
If listboxname.Selected(li) Then
ActiveSheet.Range("B2").Offset(i, 0) = listboxname.List(li)
i = i + 1
End If
Next
End Sub

I have a series of list boxes allowing multiple selections that all
have different data. I would like the output of all list boxes to
show across horizontal cells (all items from ListBox1 in their own
cell in row 1, ListBox2 in row 2, etc.). The original code works
fine, but I would like to eventually write a loop such that I can call
the sub procedure and have the lists update the horizontal rows
automatically without cluttering up my VB code and requiring a lot of
repetition.

 

[stefan onken]

hi Johan,
you can use Controls:

ctrl = "ListBox1"
MsgBox Controls(ctrl).Value

or

For i= 1 To 10
MsgBox Controls("ListBox" & i).Value
Next

stefan

 

[Dave Peterson]

And if these are listboxes from the Control toolbox toolbar that are placed on
the worksheet, you can use something like:

Dim OLEObj As OLEObject
Dim iCtr As Long
Dim lCtr As Long

For iCtr = 1 To 12 'how many listboxes?
Set OLEObj = Me.OLEObjects("Listbox" & iCtr)
With OLEObj.Object
For lCtr = 1 To .ListCount - 1
If .Selected(lCtr) Then
'do the work
End If
Next lCtr
End With
Next iCtr

I used the Me keyword. It refers to the object that owns the code. In this
case, I guessed that your code was in the worksheet module, so Me refers to that
worksheet.