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

Posted on 2012-08-10
Last Modified: 2012-08-13
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.
Question by:dtburdick
    LVL 24

    Accepted Solution

    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) 
     ' 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

    LVL 24

    Expert Comment

    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

    LVL 30

    Expert Comment


    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

    LVL 30

    Expert Comment

    '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


    Author Closing Comment

    This works, but the ListBox1.List(i, 1) needed to be ListBox1.List(i,0).

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now