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