Re: [ExcelVBA] Re: LISTBOX

 

Hi Jim

I am not sure about the problem as described in your later post, but this is a more simple way of doing what I think you are trying to achieve in your code below.

Private Sub UserForm_Initialize()
Dim nRow As Long
Dim vArray As Variant
Const LOOKFOR = "PR0010"
'
    vArray = Worksheets("Gages").Range("i1:k39000").Value
    With ListBox1

        .ColumnCount = 3
        .ColumnWidths = "50"
        For nRow = 1 To UBound(vArray)
            If vArray(nRow, 1) = LOOKFOR Then
                .AddItem vArray(nRow, 1)
                .List(.ListCount - 1, 1) = vArray(nRow, 2)
                .List(.ListCount - 1, 2) = vArray(nRow, 3)
            End If
        Next nRow
    End With
End Sub

It assumes you have a Listbox1 in the UserForm and is looking for a match in column i (which I populated with an arbitrary 38,000 random rows to test the speed), and picks up the items in columns j and k for the multi-column listbox. Notice it does not error if you set the ColumnCount to 2, it just ignores the item.

Regards.

Derek +++

 

>________________________________
> From: JIMNEELY <jimneely@yahoo.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Wednesday, 22 February 2012, 19:59
>Subject: [ExcelVBA] Re: LISTBOX
>
>
> 
>
>
>--- In ExcelVBA@yahoogroups.com, "JIMNEELY" <jimneely@...> wrote:
>>
>> I want a listbox to show items from a spreadsheet. The items have to be certain items so I was thinking a FIND would do the trick. but my main question to the group is this possible.
>>
>This might help. I am trying to fill a listbox with 3 columns based on a one find item. so i look at all "PR0008" and store the 3 columns in a array to later display in a listbox. Here is the code so far. It does not store the "DEPT" but instead the count.
>Private Sub UserForm_Initialize()
>Dim lb As msforms.ListBox
>Dim rcArray() As String
>Dim Dept As Integer
>With Worksheets("Gages").Range("I2:I25")
>Set D = .Find("PR0010", LookIn:=xlValues)
>firstaddress = D.Address
>Set D = .FindNext(D)
>While D.Address <> firstaddress
>Dept = Dept + 1
>MsgBox Dept
>MsgBox D
>ReDim Preserve rcArray(1 To Dept)
>rcArray(D) = D
>Set D = .FindNext(D)
>Wend
>End With
>
>'Place the array in the listbox
>Set lb = Me.ListBox1
>With lb
>.ColumnCount = 1
>.ColumnWidths = "50"
>.List = rcArray
>End With
>End Sub
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (10)

Recent Activity:

• New Members 5

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___