Populate listbox with unique values

[adam]

Suppose I've added a new row with a new product, category and department to the sheet. But the department does not get added to the list box 1 and the product does not get added to the listbox3 also category does not get added to the list box 2.

[HansV]

You'll have to modify all the code the way I suggested in my previous reply.

Or use Microsoft Access - you're still trying to create a database application in Excel.

[adam]

How could I make the code in Post 30478 to populate the list box 1 from a particular sheet such as "consumable".

At present the lixtbox1 gets populated with column contents of the active sheet.

[HansV]

Change the line

Set AllCells = Range("F10:F42")

to

Set AllCells = Worksheets("consumable").Range("F10:F42")

[adam]

Here is the modified version. But this isn't doing the job. What may be the reason?

Code: Select all

Private Sub ListBox1_Click()
 Dim AllCells As Range, Cell As Range
 Dim m As Long
  Dim NoDupes As New Collection
  Dim i As Integer, j As Integer
  Dim Swap1, Swap2, Item
  Dim strDept As String

  strDept = Me.ListBox1
    m = Range("F9").End(xlDown).Row
  Set AllCells = Worksheets("Consumables").Range("F10:F" & m)
  Set NoDupes = New Collection

  On Error Resume Next
  For Each Cell In AllCells
    If Cell.Offset(0, 1) = strDept Then
      NoDupes.Add Cell.Value, CStr(Cell.Value)
    End If
  Next Cell
  On Error GoTo 0

  For i = 1 To NoDupes.Count - 1
    For j = i + 1 To NoDupes.Count
      If NoDupes(i) > NoDupes(j) Then
        Swap1 = NoDupes(i)
        Swap2 = NoDupes(j)
        NoDupes.Add Swap1, Before:=j
        NoDupes.Add Swap2, Before:=i
        NoDupes.Remove i + 1
        NoDupes.Remove j + 1
      End If
    Next j
  Next i

  frmItemCategory.ListBox2.Clear
  For Each Item In NoDupes
    frmItemCategory.ListBox2.AddItem Item
  Next Item
       End Sub

[HansV]

You should obviously also change

m = Range("F9").End(xlDown).Row

to

m = Worksheets("Consumables").Range("F9").End(xlDown).Row

These lines of code are related, they are not completely independent of each other.

[adam]

I guess I've changed accordingly and still there seem to be no improvement.

What have I done wrong now or what is still being ignored here?

[HansV]

In the first place, you have modified the code behind the userform, but you haven't modified the macro that opens the userform and populates the list boxes initially.

In the second place, you use Products("Consumables") which makes no sense at all.

[adam]

Sorry! that was a misunderstanding.

are you saying that the following line should be changed?

Ray = Array("G10:G129", "F10:F129")

[HansV]

No, I'm not saying that. I'm not in favor of changing lines of code at random, I prefer to do it systematically.

The line that you had to change before was the line

Set AllCells = Range(...)

This line refers to the active sheet. In order to make it refer to another sheet, you had to modify it.

The macro named Remove also contains a line

Set AllCells = Range(...)

That's the one you have to change!

[adam]

Thanks for the help Hans.

How could I make two columns; Column D & E to appear on list box 3. Currently only the column E appears in list box 3

[HansV]

If you want to set the RowSource of ListBox3, as in your current code, you must change lines such as

ListBox3.RowSource = "Products!E10:E14"

to

ListBox3.RowSource = "Products!D10:E14"

And of course, you musty set the ColumCount property of ListBox3 to 2, otherwise you'll still see only one column.

[adam]

How could the list box 2 be made to get populated when only a row from list box 1 is clicked?
At present the list box 2 gets populated as the form loads.

Note: Meaning the same effect when the list box 3 gets populated when a row from list box 2 is clicked.

[HansV]

The macro named Remove contains code - at your explicit request - that populates both ListBox1 and ListBox2 when the form is opened. If you only want to populate ListBox1, you can modify the macro Remove accordingly.

[adam]

Thanks for the help.

I’m using the following vLook up code so that when I type a code such as 0001 in column E of the sheet “NewOrder”, the Item Description from the products sheet for that code gets copied to the column G on to the same row.

=IF(ISNA(VLOOKUP(E15,Products!$D$10:$GC$128,2,FALSE)),"",VLOOKUP(E15,Products!$D$10:$G$128,2,FALSE))

How Could I write a worksheet event code so that the item description gets copied to the column G each time I write a code number in the column E of the sheet "NewOrder" instead of having to write formula on each row.

[HansV]

Sorry, what does this have to do with the subject of this thread (populate listbox with unique values)?

[adam]

Sorry, for posting in the wrong thread.

[HansV]

Question has been reposted as Worksheet Event Code for Vlookup Formula.