ListBox Help!

[AeroAlx]

Hello!

I'm new to the forums. I'm trying to do the following...

Let's say I have in one column a list from one excel file. This list is quite long, and has duplicate entries. Now let's say I want a userform to pop up and give the user the ability to choose unique items from that list for the macro to do things with. Whatever the user enters, I basically want to put either into another range, or into a data array. I will then only do calculations / Vlookups only as long as the data uses the field that is selected.

Anyone have any idea how I can do this? I'm new to VBA, but I can forsee this as a hurdle. I tried to do some of the research for it but have not been able to find anything relevant to what I want to do.

Please Help!

[GTO]

Greetings and welcome to vbaexpress!

Let's say I have in one column a list from one excel file. This list is quite long, and has duplicate entries. Now let's say I want a userform to pop up and give the user the ability to choose unique items from that list for the macro to do things with....

Just to get that first part - I think you mean that you only want the user to be able to select values that are only listed once in the 'list'.

If that is the case, here is but one way...

(I happen to grab a combobox, but .additem for listbox as welll...

Option Explicit
    
Private Sub UserForm_Initialize()
Dim rCell   As Range
Dim MyRange As Range
    
    With Sheet1
        
        Set MyRange = .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
        
        For Each rCell In MyRange
            If Evaluate("=COUNTIF(" & MyRange.Address & "," & rCell.Address(False, False) & ")") = 1 Then
                Me.ComboBox1.AddItem rCell.Value
            End If
        Next
    End With
End Sub

Mark

[RolfJ]

Under the following assumptions the code example below should give you an idea on how to go about this:

• You added a form to your project
• You added a Listbox to the form called ListBox1

[vba]
Private Sub UserForm_Activate()
Dim r As Range
Set r = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Dim rCell As Range
For Each rCell In r.Cells
If Not IsOnList(rCell.Value) Then ListBox1.AddItem (rCell.Value)
Next rCell
End Sub

Private Function IsOnList(s As String)
Dim i As Integer
For i = 0 To ListBox1.ListCount - 1
If s = ListBox1.List(i) Then
IsOnList = True
Exit Function
End If
Next i
IsOnList = False
End Function
[/vba]

You would need to copy the code to the code section associated with the form you added.

[AeroAlx]

Thank you for your responses. What I meant to say was the list might have the same word listed in a column between 15 to 100 times. All I want the listbox to list is that same word once. There are potentially 300 of these words that are again duplicated in the same manner. If say someone selects that word then I want to be able to do somethbg with all entries associated with that one word, meaning all the duplicates will be looked at.

[mikerickson]

This assumes that your list is in column A and has a header.
Putting this in the userform's code module will get the unique elements in the list.
Selecting one of those elements will activate a filter so that only those rows are visible.
[VBA]Dim dataRange As Range, CritRange As Range

Private Sub ListBox1_AfterUpdate()
If ListBox1.ListIndex <> -1 Then
CritRange.Cells(1, 1).Value = dataRange.Cells(1, 1).Value
CritRange.Cells(2, 1).Value = ListBox1.Value
dataRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=False, criteriarange:=CritRange
End If
End Sub

Private Sub UserForm_Initialize()
Dim rngUniques As Range, oneCell As Range
With ThisWorkbook.Sheets("Sheet1")
With .Range("A:A")
Set dataRange = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
With .UsedRange
Set CritRange = .Cells(1, .Columns.Count + 2).Resize(2, 1)
CritRange.Cells(1, 1).Value = dataRange.Cells(1, 1).Value
End With
End With
With dataRange
On Error Resume Next
.Parent.ShowAllData
On Error GoTo 0
.AdvancedFilter xlFilterInPlace, Unique:=True
On Error Resume Next
Set rngUniques = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If Not rngUniques Is Nothing Then
For Each oneCell In rngUniques
If CStr(oneCell) <> vbNullString Then
UserForm1.ListBox1.AddItem CStr(oneCell)
End If
Next oneCell
End If
End Sub

Private Sub UserForm_Terminate()
CritRange.EntireColumn.Delete
On Error Resume Next
dataRange.Parent.ShowAllData
On Error GoTo 0
End Sub[/VBA]