[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2012-08-10
Medium Priority
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
  • 2
  • 2
LVL 24

Accepted Solution

Steve earned 2000 total points
ID: 38283166
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

ID: 38283174
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 31

Expert Comment

ID: 38283640

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 31

Expert Comment

ID: 38283673
'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

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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

830 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