Sorry cmdecker2, I don't understand what you're code is doing.
I don't think this will help me.I thinks It is easier.
Only thinh I want is fill a Listbox with a horizontal range from Q
till BQ7 !!!!!!!!
cmdecker2 wrote:
*He
subSetupform()
For Each c In frmAddEntry.Controls
If Left(c.Name, 2) = "cb" Then
c.List = CreateList(YourRangeAddress)
End If
Next c
End Sub
Function CreateList(myRange)
Dim myControl
Dim mystring As String
Dim Cell As Range
Dim NoDupes As New Collection
Dim i As Integer
Dim j As Integer
Dim Swap1, Swap2, Item
Dim cbList() As Variant
' The next statement ignores the error caused by attempting to add
' a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In Range(myRange)
If Cell.Value < "" Then NoDupes.Add Cell.Value, Cell.Value
' Note: the 2nd argument (key) for the Add method must be a
string
Next Cell
On Error GoTo 0
' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i
' Add the sorted, non-duplicated items to a ComboBox
ReDim cbList(NoDupes.Count) 'reset to same number as in no dupes
j = 0
For Each Item In NoDupes
j = j + 1
If Item < "" Then cbList(j) = Item
Next Item
For j = 1 To NoDupes.Count ' Remove names from the collection.
NoDupes.Remove 1 ' removes the current first member on each
iteration.
Next
CreateList = cbList()
End Function
Now go buy a Power Programming book by :John WalkenbachBES
investment
you'll ever make!
Carl
John Walkenbac
h
"Stift " wrote in message
...
Simon Lloyd wrote:
*Why not create your list box using data validation?.....selec
your
range and give it a name...........then go to the range that
you
want the list box to be available on, highlight the whole rang
then
click on data, validation....then choose allow list and type i
the
list name you should now find that all the data in the range on
previous page will be in listbox form.
Simon *
Yes this works if the Listbox is stored in the excel worksheet bu
not
in VB.
---
Message posted from http://www.ExcelForum.com/
--
Message posted from
http://www.ExcelForum.com