Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting dates from ListBoxes by using RefEdit Control
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 a 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting dates from ListBoxes by using RefEdit Control
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 -- Regards, Tom Ogilvy "wpllc2004" wrote in message om... 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 a 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting dates from ListBoxes by using RefEdit Control
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 -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "wpllc2004" wrote in message om... 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 a 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting dates from ListBoxes by using RefEdit Control
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
start and end dates within listboxes | Excel Worksheet Functions | |||
Move from cell A1 to R362 by selecting control tool or something | Excel Discussion (Misc queries) | |||
Selecting Refedit value | Excel Programming | |||
Shift-Control Arrow and RefEdit Control? | Excel Programming | |||
RefEdit control bug | Excel Programming |