Two Column list box with sheet value

Question

I am a newbie. (pardon my English)

I have created an invoice for myself, there is a view user-form which initialize with this code:

 Private Sub UserForm_Initialize()

  'Populate listbox with unique invoice numbers from sheet "Invoice data"

  Dim Test As New Collection
  Dim rng As Variant
  Dim Value As Variant

  'Identify range
  rng = Sheets("Invoice data").Range("A2:A" & _
  Sheets("Invoice data").Columns("A").Find("*", _
  SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
  LookIn:=xlValues).Row)

  'Filter unique values
  On Error Resume Next
  For Each Value In rng
    If Len(Value) > 0 Then Test.Add Value, CStr(Value)
  Next Value
  On Error GoTo 0

  For Each Value In Test
    ListBox1.AddItem Value
  Next Value

  ListBox1.ListIndex = 0

End Sub

In this user-form I have a list-box to show the id number in the sheets ("invoice data") Range is column "A".

I need to convert this list box into a two column box, the first column should show "A" and next column should show "C".

Can you guide me?

Thanks in advance.

First hit on Google: https://stackoverflow.com/questions/11213962/vba-listbox-multicolumn-add — Tim Williams, Aug 08 '17 at 05:18
What exactly is the problem? How to make a two-column listbox? Maybe this will help - http://software-solutions-online.com/vba-multi-column-listboxes/ If you use a Dictionary instead of a Collection, you can more easily check for unique values, adding to the list as you go (and that means you can aslo add the "c" value (`Value.Offset(0,2).Value`). BTW I'd avoid using `Value` as a variable name... — Tim Williams, Aug 08 '17 at 06:27
Dear Tim Williams, I can make two-column box well.. I am not a well programmer (actually I m a designer) above code is not mine, so i cant understand. in the userform, there is a listbox, currently it shows only the values of "A" columns, now i need "C" columns too... look into the code, it is filtering values... — Aadavan Naveen, Aug 08 '17 at 08:22
I think @TimWilliams answer does resolve the problem. Issue might be that you don't know how to implement the solution from those answers into the code above? Most people here are more than happy to help but unfortunately this is not a code writing site. Please try the solutions provided and then if you have any issue, update your code and highlight where the issue is and what it is. More than likely,this will yield a more favourable response — Zac, Aug 08 '17 at 10:07

score 0 · Answer 1 · answered Aug 08 '17 at 15:59

Using a Dictionary to track unique values:

Private Sub UserForm_Initialize()

    'Populate listbox with unique invoice numbers from sheet "Invoice data"
    Dim dict As Object, sht As Worksheet
    Dim rng As Range, c As Range, v, i As Long

    Set dict = CreateObject("scripting.dictionary")
    Set sht = Sheets("Invoice data")

    'Identify range
    Set rng = sht.Range("A2:A" & _
        sht.Columns("A").Find("*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row)

    'Filter unique values
    For Each c In rng.Cells
        v = c.Value
        'any value to add?
        If Len(v) > 0 Then
            'new value?
            If Not dict.exists(v) Then
                With Me.ListBox1
                    .AddItem
                    .List(i, 0) = v
                    .List(i, 1) = c.Offset(0, 2).Value
                End With
                i = i + 1
                dict.Add v, 1
            End If
        End If
    Next c

    Me.ListBox1.ListIndex = 0

End Sub

Two Column list box with sheet value

1 Answers1