View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] johan.mcconnell@gmail.com is offline
external usenet poster
 
Posts: 1
Default Naming a listbox as a string

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.