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....
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.