Hello all,

i have a problem with my VB project...

i have a user form that should display a list of dates in
a column, dependent on the data held in column "AX" on a
spreadsheet. i have this bit of code already in my program
but...

Dim rng As Range
Dim v As Variant
Dim coll As Collection
Set coll = New Collection
Set rng = Range("AX1")
On Error Resume Next
Do Until rng.Value = ""
coll.Add rng.Text, rng.Text
Set rng = rng(2, 0)
Loop
With timetable.ComboBox1
For Each v In coll
.AddItem
Next v
.ListIndex = 0
End With
timetable.Show

....when ran, it just shows empty fields in the list!!!!

and....

also not all the records in column "AX" have dates in,
therefore it will end as soon as it hits a blanc cell.

can anyone help?? and i need it to find all the results
until it reaches my final row!

thank you,

Robert Couchman

Robert,

You are not loading the combobox, so that is why you see nothing. But why
are you loading via a collection, it seems to serve no purpose that I can
ascertain.
Try this

Dim rng As Range
Dim v As Variant
Dim coll As Collection
Dim i As Long

Set coll = New Collection
Set rng = Range("AX1")
With timetable.Combobox1
For i = 1 To Cells(Rows.Count, "AX").End(xlUp).Row
If Cells(i, "AX").Value < "" Then
.AddItem Format(Cells(i, "AX"), "dd mmm yyyy")
End If
Next i
.ListIndex = 0
End With
timetable.Show



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robert Couchman" wrote in message
...
Hello all,

i have a problem with my VB project...

i have a user form that should display a list of dates in
a column, dependent on the data held in column "AX" on a
spreadsheet. i have this bit of code already in my program
but...

Dim rng As Range
Dim v As Variant
Dim coll As Collection
Set coll = New Collection
Set rng = Range("AX1")
On Error Resume Next
Do Until rng.Value = ""
coll.Add rng.Text, rng.Text
Set rng = rng(2, 0)
Loop
With timetable.ComboBox1
For Each v In coll
.AddItem
Next v
.ListIndex = 0
End With
timetable.Show

...when ran, it just shows empty fields in the list!!!!

and....

also not all the records in column "AX" have dates in,
therefore it will end as soon as it hits a blanc cell.

can anyone help?? and i need it to find all the results
until it reaches my final row!

thank you,

Robert Couchman

Bob,

thank you for the help!! at least now i can see my
values!!!
the only problems i have though are the following...

1) i seem to get the column heading appear in my list (not
needed!)

2) the data in the records appears more than once,
therefore i seem to get the same date appearing in my list

3) the combobox auto selects the first result (need a
blank cell)

4) the data is not in an order, it appears as it does in
my records (need a sort function)

but... the code actually works ok,

so thank you!

Robert Couchman

Robert,

Now I know why you had a collection<G.

Try this instead

Dim v As Variant
Dim coll As Collection
Dim i As Long

Set coll = New Collection
Set rng = Range("AX2")
On Error Resume Next
For i = 1 To Cells(Rows.Count, "AX").End(xlUp).Row
If Cells(i, "AX").Value < "" Then
coll.Add Cells(i, "AX").Text, Cells(i, "AX").Text
End If
Next i
On Error GoTo 0
With timetable.Combobox1
For Each v In coll
.AddItem v
Next v
.ListIndex = 0
End With
timetable.Show

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robert Couchman" wrote in message
...
Bob,

thank you for the help!! at least now i can see my
values!!!
the only problems i have though are the following...

1) i seem to get the column heading appear in my list (not
needed!)

2) the data in the records appears more than once,
therefore i seem to get the same date appearing in my list

3) the combobox auto selects the first result (need a
blank cell)

4) the data is not in an order, it appears as it does in
my records (need a sort function)

but... the code actually works ok,

so thank you!

Robert Couchman

Hello Bob,

good news we have managed to illiminate 3 out of 4 of my
original problems in less than half an hour!!

3) the combobox auto selects the first result (need a
blank cell)

**answer** set the listindex to -1 so when it opens it has
a record that is blank!!!!

4) the data is not in an order, it appears as it does in
my records (need a sort function)

Now for this final question, how do we sort the data so it
appears in the list in a logical order???

thank you,

Robert Couchman

Hi Robert,

Final problem

Dim rng As Range
Dim v As Variant
Dim coll As Collection
Dim i As Long

Set coll = New Collection
Set rng = Range("AX2")
Columns("AX:AX").Sort Key1:=Range("AX2"), _
Order1:=xlAscending, _
Header:=xlYes
On Error Resume Next
For i = 2 To Cells(Rows.Count, "AX").End(xlUp).Row
If Cells(i, "AX").Value < "" Then
coll.Add Cells(i, "AX").Text, Cells(i, "AX").Text
End If
Next i
On Error GoTo 0
With timetable.Combobox1
For Each v In coll
.AddItem v
Next v
.ListIndex = 0
End With
timetable.Show

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robert Couchman" wrote in message
...
Hello Bob,

good news we have managed to illiminate 3 out of 4 of my
original problems in less than half an hour!!

3) the combobox auto selects the first result (need a
blank cell)

**answer** set the listindex to -1 so when it opens it has
a record that is blank!!!!

4) the data is not in an order, it appears as it does in
my records (need a sort function)

Now for this final question, how do we sort the data so it
appears in the list in a logical order???

thank you,

Robert Couchman

Hello Bob,

***1) i seem to get the column heading appear in my list
(not needed!)***

NO LONGER A PROBLEM!!!

i noticed that i was counting from 1 where row 1 is the
column headings, therefore if the 1 was changed to a 2,
then it would count from the first data entry!

thank you,

now for the other problems!!!!

Robert Couchman