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

x
?
Solved

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

Posted on 2012-08-10
5
Medium Priority
?
434 Views
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.
0
Comment
Question by:dtburdick
  • 2
  • 2
5 Comments
 
LVL 24

Accepted Solution

by:
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) 
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
 
LVL 24

Expert Comment

by:Steve
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

0
 
LVL 31

Expert Comment

by:hnasr
ID: 38283640
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
 
LVL 31

Expert Comment

by:hnasr
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

0
 

Author Closing Comment

by:dtburdick
ID: 38288171
This works, but the ListBox1.List(i, 1) needed to be ListBox1.List(i,0).
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