PDA

View Full Version : ListBox Help!



AeroAlx
01-19-2010, 11:45 PM
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
01-20-2010, 12:31 AM
Greetings and welcome to vbaexpress!:thumb


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
01-20-2010, 12:47 AM
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
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


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

AeroAlx
01-20-2010, 08:28 AM
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
01-20-2010, 06:27 PM
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.
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