PDA

View Full Version : Solved: Contents of a dropdown Box



gibbo1715
12-20-2005, 07:43 AM
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
12-20-2005, 07:59 AM
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

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

lucas
12-20-2005, 08:12 AM
Try this Gibbo:

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

gibbo1715
12-20-2005, 08:32 AM
Thanks Steve I ll give it a go

Gibbo

Bob Phillips
12-20-2005, 04:27 PM
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

excelliot
12-26-2005, 12:25 AM
gr8 piece of code again can u explain thess two lines



Dim oUniques As Collection




oUniques.Add CStr(cell.Value), CStr(cell.Value)

Bob Phillips
12-26-2005, 03:23 AM
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
12-26-2005, 04:33 AM
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:


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


Kind regards,
Dennis

ijswalker
02-16-2006, 01:48 PM
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
02-16-2006, 01:51 PM
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
02-16-2006, 02:25 PM
Hello Ian,
I think Gibbo was trying to create a list of unique items from the range....