Solved: Contents of a dropdown Box

[gibbo1715]

All

I need to create a dropdown box on a userform that contains the unique items in a list A3:A1103

Can anyone tell me the simplest way to do this please

Thanks

Gibbo

[gibbo1715]

The code below will get my data for me, i can then set this as a variable named range i guess and link my combo to that

If anyone has a quicker method let me know

cheers

Gibbo

[VBA] Private Sub CommandButton1_Click()
Dim AnArray() As String, i As Long
AnArray = GetUniqueEntries(Workbooks("Outstanding").Sheets("Data").Range("F9:F10000") )
If Len(AnArray(0)) > 0 Then
For i = 0 To UBound(AnArray)
Range("A1").Offset(i, 0) = AnArray(i)
Next
End If
End Sub

Function GetUniqueEntries(ByVal ARange As Range) As String()
Dim TempArr() As String, Cnt As Long, CLL As Range, i As Long
Cnt = 0
i = 0
ReDim TempArr(0)
Set ARange = Intersect(ARange, ARange.Parent.UsedRange)
If Not ARange Is Nothing Then
For Each CLL In ARange.Cells
If Len(Trim(CLL.Text)) > 0 Then
For i = 0 To Cnt - 1
If TempArr(i) = CLL.Text Then Exit For
Next i
If i = Cnt Then
ReDim Preserve TempArr(Cnt)
TempArr(Cnt) = CLL.Text
Cnt = Cnt + 1
End If
End If
Next
End If
GetUniqueEntries = TempArr
End Function [/VBA]

[lucas]

Try this Gibbo:

[VBA] Private Sub UserForm_Initialize()

Dim UniqueList() As String
Dim x As Long
Dim Rng1 As Range
Dim c As Range
Dim Unique As Boolean
Dim y As Long

Set Rng1 = Sheets("Sheet1").Range("A3:A1103")
y = 1

ReDim UniqueList(1 To Rng1.Rows.Count)

For Each c In Rng1
If Not c.Value = vbNullString Then
Unique = True
For x = 1 To y
If UniqueList(x) = c.Text Then
Unique = False
End If
Next
If Unique Then
y = y + 1
Me.ComboBox1.AddItem (c.Text)
UniqueList(y) = c.Text
End If
End If
Next

End Sub

[/VBA]

[gibbo1715]

Thanks Steve I ll give it a go

Gibbo

[Bob Phillips]

[vba]

Private Sub CommandButton1_Click()
Dim oUniques As Collection
Dim cell As Range
Dim i As Long
Set oUniques = New Collection
On Error Resume Next
For Each cell In Range("a3:A1103")
oUniques.Add CStr(cell.Value), CStr(cell.Value)
Next
On Error GoTo 0

For i = 1 To oUniques.Count
Cells(i, "B").Value = oUniques(i)
Next i

End Sub
[/vba]

[excelliot]

gr8 piece of code again can u explain thess two lines

[vba]
Dim oUniques As Collection
[/vba]

[vba]
oUniques.Add CStr(cell.Value), CStr(cell.Value)
[/vba]

[Bob Phillips]

gr8 piece of code again can u explain thess two lines

It just initiates a collection, and then adds to that collection. I used a collection as you cannot have duplicates ther, so the object handles it for me.

[XL-Dennis]

Season Greetings to everyone

An alternative approach is to create a unique list with Advanced Filter, read the list into a variant array and populate the combobox with the variant array as the following example shows:

[vba]
Option Explicit
Private Sub UserForm_Initialize()
'The approach assume that it exist a columnname in cell A2
Dim wsSource As Worksheet
Dim rnSource As Range
Dim rnUnique As Range
Dim vaUnique As Variant
Set wsSource = ThisWorkbook.Worksheets("Sheet1")
With wsSource
Set rnSource = .Range("A2:A1103")
End With
rnSource.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=wsSource.Range("J1"), Unique:=True

With wsSource
Set rnUnique = .Range("J2:J" & .Cells(.Rows.Count, "J").End(xlUp).Row)
End With
With rnUnique
vaUnique = .Value
.Offset(-1, 0).Resize(.Rows.Count + 1, 1).ClearContents
End With

With Me.ComboBox1
.Clear
.List = vaUnique
.ListIndex = -1
End With
End Sub
[/vba]

Kind regards,
Dennis

[ijswalker]

I hope I'm not missing the point here but when yoou put a combo box on a userform you can then look at the properties of the combo box.

There is an item called ROWSOURCE. Type in your range there e.g A1:A1103 and run your userform. it will show the list in your combox.

If you need to reference a range from a particular sheet then use

Sheet1!A1;A1103 if the name of the sheet is called Sheet1.

Hope that helps!

Ian

[ijswalker]

Hi,

Go to combobox properties.

ROWSOURCE : Type in A1:A1103

This should show your list in the combobox when you load your userform.

Hope that helps

Ian

[lucas]

Hello Ian,
I think Gibbo was trying to create a list of unique items from the range....