How do I copy/clone an Excel listbox into a variable to work with?

I want to work with a copy of the listbox rather than the original because it fires off code when I loop through the values.
dtburdickAsked:
Who is Participating?
 
SteveConnect With a Mentor Commented:
this may get you started:
Using Array(b) with data from single listbox

Dim b() 
Dim j As Integer 
Dim i As Integer 
 
Redim b(1 To ListBox1.ListCount, 1 To 1) 
 
 ' to add listbox to array
For i = 0 To ListBox1.ListCount 
        j = j + 1: b(j, 1) = ListBox1.List(i, 1) 
Next 
 
 ' to add array to listbox
For i = 1 To UBound(b, 1) 
    If Not IsEmpty(b(i, 1)) Then ListBox1.AddItem b(i, 1) 
Next i 

Open in new window

0
 
SteveCommented:
Also this may work:

Sub Test()

Dim vntData As Variant 

'listbox to array:   
vntData = ListBox1.List 
     
'array to listbox:
ListBox1.List = vntData 

End sub

Open in new window

0
 
hnasrCommented:
Try:

Dim objList As Object
Set objList=ListBox1

' you can use objList in place of ListBox1. 

'You may declare the variable in a module to use it globally.

Public objList as Object

Open in new window

0
 
hnasrCommented:
'To fill an array: Use preserve keyword to keep previous items.
' This is a string variable and not to be manipulated as List Box.

    Dim lb() As String
    Dim i, j As Integer
    j = -1
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            j = j + 1
            ReDim Preserve lb(j)
            lb(j) = ListBox1.List(i)
       End If
    Next i

Open in new window

0
 
dtburdickAuthor Commented:
This works, but the ListBox1.List(i, 1) needed to be ListBox1.List(i,0).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.