Tukukkk

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?

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.