?
Solved

Create List box from an array

Posted on 2010-03-25
2
Medium Priority
?
273 Views
Last Modified: 2012-06-21
Hi all, I've VBA code that brings data from a network drive csv file. I want to at run time be promted to select from a list box an object in the array (trailer) and continue to run based upon my selection, this is the current code;

Sub RunLoad()
'
' RunLoad Macro
' Macro 22/03/2010 by bez66


    Sheets("Load Details").Select
   
    Dim x1, x2, x3, x4, trailer, itemCode, itemUpc, itemDescription, uom As String
    Dim quantity, total As Double
    Dim j, row, column, count, xcount, x, h, a As Integer
    Dim found, xfound As Boolean
   
    Dim trailers(100)
    Dim itemCodes(500, 2)
   
    Range("A6:Z1000").ClearContents
    Close
    'Open "h:\freshcook\paul\desp20.txt" For Input As 1
    Open "z:\desp20.txt" For Input As 1
    Do Until EOF(1)
        Input #1, x1, x2, x3, x4, trailer, itemCode, itemUpc, itemDescription, quantity, uom
        If x1 <> "0" Then
                       
            'create trailer array - sets column for each trailer number
            found = False
            For j = 1 To count
                If trailer = trailers(j) Then
                    found = True
                    Exit For
                End If
            Next
            If found = False Then
                count = count + 1
                trailers(count) = trailer
                                               
            End If
                       
            'create itemCode array - sets row for each item code
            xfound = False
            For x = 1 To xcount
                If itemCode = itemCodes(x, 1) Then
                    xfound = True
                    Exit For
                End If
            Next
            If xfound = False Then
                xcount = xcount + 1
                row = row + 1
                itemCodes(xcount, 1) = itemCode
                itemCodes(xcount, 2) = row
            End If
                                   
            'output data
            Cells(x + 6, 1) = itemCode
            Cells(x + 6, 2) = itemUpc
            Cells(x + 6, 3) = itemDescription
            Cells(x + 6, j + 3) = quantity
            Cells(6, j + 3) = arr
                   
        End If
       
    Loop
   
       
    'row totals
    h = 6
    Cells(6, count + 4) = "TOTALS"
    Do
        h = h + 1
        If Cells(h, 1) = "" Then Exit Do
        For a = 1 To count
            total = total + Cells(h, a + 3)
        Next
        Cells(h, a + 3) = total: total = 0
    Loop
   
   
    'column totals
    row = 6
    column = 3
    total = 0
    Do
        column = column + 1
        If Cells(6, column - 1) = "TOTALS" Then Exit Do
        Do
            row = row + 1
            If Cells(row, 1) = "" Then Exit Do
            total = total + Cells(row, column)
        Loop
        Cells(row, column) = total
        total = 0
        row = 6
    Loop




End Sub
0
Comment
Question by:Bez66
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 28534895
The simplest thing would be to have a userform with a listbox, then assign the trailers array to the Listbox's List property.
0
 

Author Closing Comment

by:Bez66
ID: 31706945
Many thanks for the advice.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

601 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