CODEWITHSUNDEEP
excelvba
Edrisa Gharibyar
Edrisa Gharibyar

Reputation: 1

Excel VBA populating a list box with values from selected sheet using loops

I have 2 listboxes in a userform. When the user clicks on an item (a worksheet name) in one of the boxes, the other list box should autopopulate with values in the D column of the specific worksheet they have selected. I also dont want any duplicate values to show up. How can i accomplish this by using loops?

Upvotes: 0

Views: 2311

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

Well, a few things are going to have to happen here.

Since you failed to state the names of your listboxes, we are using the generic forms:

  • ListBox1 is the list box with your sheet names
  • ListBox2 is the list box to auto-populate from column D

First, you will need to ensure that listBox1 is initialized with your worksheet values. A sample of how you could do this is:

Private Sub UserForm_Initialize()

    'MUST BE PLACED IN USERFORM CODE MODULE!!
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Me.ListBox1.AddItem ws.Name
    Next ws

End Sub

The above is not required for your task, but your userform being pre-populated is required.

Now you are going to want to watch for the ListBox1_Change() event. Again, if your listbox isn't named ListBox1, then you will need to change this in the Sub Name.

Private Sub ListBox1_Change()

    'MUST BE PLACED IN USERFORM CODE MODULE!!
    Dim wsSelected As Worksheet
    Set wsSelected = ThisWorkbook.Worksheets(Me.ListBox1.Value)

    With wsSelected

        Me.ListBox2.List = rngToUniqueArr(.Range(.Cells(1, "D"), .Cells( _
                lastRow(wsSelected, "D"), "D")))

    End With

End Sub

Function rngToUniqueArr(ByVal rng As Range) As Variant
    'Reference to [Microsoft Scripting Runtime] Required
    Dim dict As New Scripting.Dictionary, cel As Range
    For Each cel In rng.Cells
        dict(cel.Value) = 1
    Next cel
    rngToUniqueArr = dict.Keys
End Function

Function lastRow(ws As Worksheet, Optional col As Variant = 1) As Long
    With ws
        lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
End Function

The sub rngToUniqueArr is something I have stored in my PERSONAL.XLSB workbook, it's a useful function to have. This will take an input range and create a dictionary of unique values, and output into an array.

The Above requires a reference to be set to Microsoft Scripting Runtime in Tools > References

In your ListBox1_Change() event, the wsSelected is the worksheet you selected from ListBox1, and you are adding the array from the rngToUniqueArr into ListBox2.

Upvotes: 1

Related Questions