Selecting dates from ListBoxes by using RefEdit Control

[wpllc2004]

Hello to all

I've a UserForm that contains 2 Listboxes:
- lbxData is a multicolumn listbox that contains data passed from a
RefEdit control.
- lbxSelectDate is a one column listbox that has Dates passed from the
3rd column of the RefEdit control.
My questions are:

a)The code for adding the dates in lbxSelectDate is the following:
Private Sub AddDates()
Dim cell as Range, AllCellsDate as Range
Dim UniqueDates as New Collection
Set AllCellsDate = ("C3:C1000")
On Error Resume Next
For Each cell In AllCellsDate
If IsDate(cell.Value) Then
UniqueDates.Add cell.Value, CStr(cell.Value)
Else
Exit For
End If
Next cell
For Each Item In UniqueDates
UserForm1.lbxSelectDate.AddItem Item
Next Item
End Sub

Although this is working quite well I would prefer to have a code that
relates AllCellsDate with the RefEdit control (I want to use the third
column, from rows 3 to the last). Is there a way to access the 3rd
column of the RefEdit control?


b) After having the unique dates passed to the lbxSelectDate I want
the user to be able to choose dates (it's a multiselect extended list
box) so that subsequently the appropriate checkboxes in the lbxData
(the main list box) to be selected automatically. Furthermore, when a
date is not highlighted any more I would like the corresponding boxes
to be unchecked. The code that I came up so far is the following but
it doesn't work at all. Can anyone help me?

Private Sub lbxSelectDate_Click()
Dim r As Integer
If lbxSelectDate.Value = True Then
For r = 0 To lbxData.ListCount - 1
If CDate(lbxData.List(r, 2)) = lbxSelectDate.Value Then
lbxData.Selected(r) = True
End If
If CDate(lbxData.List(r, 2)) <> lbxSelectDate.Value Then
lbxData.Selected(r) = False
End If
Next r
End If
End Sub


Many thanks in advance for your suggestions and sorry for the lengthy
request

 

[Tom Ogilvy]

Dim cell as Range, AllCellsDate as Range
Dim UniqueDates as New Collection
Set AllCellsDate = Range(Userform1.RefEdit1.Value)




Private Sub lbxSelectDate_Change()
Dim r As Integer
For r = 0 To lbxSelectDate.Listcount- 1
lbxData.Selected(r) = lbxSelectDate.Selected(r)
Next
End Sub

 

[Tom Ogilvy]

Whoops, I believe you said column 3 of the refedit reference
Dim cell as Range, AllCellsDate as Range
Dim UniqueDates as New Collection
Set AllCellsDate = Range(Userform1.RefEdit1.Value).columns(3).Cells

 

[wpllc]

Tom, thanks a lot for the reply.

The first part works smoothly however the second one however doesn't
seem to work yet (at least it doesn't crash as it used to with my code).

There should be a problem with handling the dates I think.Do I need to
use a function to convert my dates to a specific data type because in
the lbxSelectDate listbox I have strings while in the main listbox
lbxData I have dates?

Can you or anyone else help me cause I hate handling dates?

Thanks again in advance

AP