Insert cells in a ListBox
 

I've got some data stored in a few cells
the range where the data stored is in sheet "Database"
from Q7-BQ7.

I've got another Combobox with cellrange :Database!C9:C63 and thi
gives no problems. Database!Q7:BQ7 seems not to work.

PLEASE HEEEEEEEEEEEEEEEEEEELLLLP !!

--
Message posted from http://www.ExcelForum.com

Insert cells in a ListBox
 

Why not create your list box using data validation?.....select you
range and give it a name...........then go to the range that you wan
the list box to be available on, highlight the whole range then clic
on data, validation....then choose allow list and type in the list nam
you should now find that all the data in the range on previous pag
will be in listbox form.

Simo

--
Message posted from http://www.ExcelForum.com

Insert cells in a ListBox
 

Simon Lloyd wrote:
*Why not create your list box using data validation?.....select you
range and give it a name...........then go to the range that yo
want the list box to be available on, highlight the whole range the
click on data, validation....then choose allow list and type in th
list name you should now find that all the data in the range o
previous page will be in listbox form.

Simon *


Yes this works if the Listbox is stored in the excel worksheet but no
in VB

--
Message posted from http://www.ExcelForum.com

Insert cells in a ListBox
 

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 WalkenbachBEST 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?.....select 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 range then
click on data, validation....then choose allow list and type in 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 but not
in VB.


---
Message posted from http://www.ExcelForum.com/

Insert cells in a ListBox
 

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