Using a row source for a listbox??

[Todd_M]

Hi-
Here my set up on sheet1:
Column "A" has a list of names(rows 1-50)
Column "B" has a list of dates next to each name that refers to that persons starting date of employment.
Column "C" has a list of dates that refers to that persons end date of employment.
Now lets assume that all fifty cells in column "A" are filled with names (employees) and all employees have a start date in column "B". However only 40 employees have an end date listed next to there name in column "C". That leaves you with 10 "active" employees who still are employed. On sheet1 these 10 people that are still active have no end date in column "C" listed in the same row as there name and those names are not one after the other.ex:


column "A" column "B" column "C"
John 26 feb 02 13 mar 02
Andy 28 feb 02
Jason 1 mar 02 12 mar 02
Steven 2 mar 02 13 mar 02
Eddy 2 mar 02
Jean 7 mar 02 20 mar 02.....


Now my question is, using a list box on a userform How do you set the row source property in code to only show the "acvtive" employees. This list on sheet one is always being updated w/ new employees(column "A") and end dates(column "C"), so there for once an employee is asigned an end date, The name can not show on the listbox. By the way, the only column to be shown from the list box is column "A", there name. I hope someone understands this-
thank you- Todd

[nisht]

Sub addlista()

dim strname() as string
dim rangea as range
dim c as range
set rangea = range("a1:a20")

For each c in rangea
i = 0
if isdate(c.offset(i,2).value) and Not isdate(c.offset(i,3).value then
redim preserve strname(i)
strname(i) = c.value
i = 1+i
next c
cbocombobox1.list = strname()

end sub

I have not check above code.. but hope it will work.
nishith desai
nisht@pexcel.com
you can download a example file 16 from

http://www.pexcel.com/download.htm

[Dave Hawley]

Hi Tod

You may want to look at using the AutoFilter on your range to show only the needed criteria, then set the RowSource to the range again, eg

ListBox1.RowSource=""
ListBox1.RowSource="MyRange"

Where "myRange" is a dynamic named range.
http://www.ozgrid.com/Excel/DynamicRanges.htm
The List box should only show the visible rows, but you will need to run the:

ListBox1.RowSource=""
ListBox1.RowSource="MyRange"

Each time.

[Aladin Akyurek]

On 2002-03-25 07:21, Todd_M wrote:
Hi-
Here my set up on sheet1:
Column "A" has a list of names(rows 1-50)
Column "B" has a list of dates next to each name that refers to that persons starting date of employment.
Column "C" has a list of dates that refers to that persons end date of employment.
Now lets assume that all fifty cells in column "A" are filled with names (employees) and all employees have a start date in column "B". However only 40 employees have an end date listed next to there name in column "C". That leaves you with 10 "active" employees who still are employed. On sheet1 these 10 people that are still active have no end date in column "C" listed in the same row as there name and those names are not one after the other.ex:


column "A" column "B" column "C"
John 26 feb 02 13 mar 02
Andy 28 feb 02
Jason 1 mar 02 12 mar 02
Steven 2 mar 02 13 mar 02
Eddy 2 mar 02
Jean 7 mar 02 20 mar 02.....


Now my question is, using a list box on a userform How do you set the row source property in code to only show the "acvtive" employees. This list on sheet one is always being updated w/ new employees(column "A") and end dates(column "C"), so there for once an employee is asigned an end date, The name can not show on the listbox. By the way, the only column to be shown from the list box is column "A", there name. I hope someone understands this-
thank you- Todd

Are you interested in a formula-based, fully automatic, system that computes the desired list?

[Todd_M]

Sure, but what do you mean??

[Aladin Akyurek]

On 2002-03-25 09:36, Todd_M wrote:
Sure, but what do you mean??

A dynamic range name that you can use in your ListBox.

[VTTW]

I'm not Todd, but I need basically the same thing. Could you explain furthur??

Thanks

[Aladin Akyurek]

On 2002-03-25 11:58, VTTW wrote:
I'm not Todd, but I need basically the same thing. Could you explain furthur??

Thanks

OK.

Lets say that A1:C7 houses the sample data Todd provided:

{"name","startdate","enddate";
"John",37313,37328;
"Andy",37315,"";
"Jason",37316,37327;
"Steven",37317,37328;
"Eddy",37317,"";
"Jean",37322,37335}

The empty cells [that is, ""] stand for no end date.

Activate Insert|Name|Define.
Enter EndRow as name in the 'Names in Workbook' box.
Enter as formula in the 'Refers to' box:

=MATCH(9.99999999999999E+307,Sheet1!$B:$B)-1

Sheet1 is the sheet where the sample data is. Column B in this sheet, as said above, houses the start dates. The -1 bit takes into account the fact that the actual data start in row 2.

Click OK.

Sheet1:

In E2 enter and copy down as far as needed:

=IF(AND(ISNUMBER(B2),LEN(C2)=0),RANK(B2,OFFSET($B$2,0,0,EndRow,1)),"")

In F2 enter and copy down as far as needed:

=IF(ISNUMBER(E2),RANK(E2,OFFSET($E$2,0,0,EndRow,1)),"")

In G2 enter and copy down as far as needed:

=IF(ROW()-1<=COUNT(OFFSET($F$2,0,0,EndRow,1)),OFFSET($A$2,MATCH(ROW()-1,OFFSET($F$2,0,0,EndRow,1),0)-1,0),"")


We get in G the following desired list of names:

{"Andy";
"Eddy"}

Activate Insert|Name|Define.
Enter Rsource as name in the 'Names in Workbook' box.
Enter as formula in the 'Refers to' box:

=OFFSET(Sheet1!$G$2,0,0,MAX(Sheet1!$F:$F),1)

Click OK.

Rsource now can be used as Input range in a ListBox or ComboBox created via Toolbars|Forms and also as source in a dropdown list created with data validation.

Note that the data area is free to change as long as RANK formulas are copied down as far enough as needed.

Aladin





[ This Message was edited by: Aladin Akyurek on 2002-03-25 12:57 ]

[Todd_M]

Hi-
I figured out another way, asuming the same sanario. VTTW if your using userforms as I am to insert your data into column "A","B" and "C" then what you can do is set up another column, lets say column "J" that will list just the name(the same name as you put in column "A"). Use
[J1].End(xlDown).Offset(1, 0).Select
ActiveCell.Offset(0, 0).Value = TextBox1.Text

This code will list the name in column "J" in the order you put them in (No empty cells will show").

Now lets say column "C" you insert a end date using a userform , then you can have a code that reads:

If Application.WorksheetFunction.IsText _
(ActiveCell.Offset(0, 3).Value) = True Then
Range("J:J").Find(TextBox1.Text).ClearContents

The name of the employee is typed into texbox1, and when an end date apears next to that employees name, the employees name in column "J" is cleared. So now if you set your row source to J1:J50 for your list box, only the names that have no end date will show in the listbox. The only down fall (which maybe someone could help me with) is that there is a space between any name that is deleted in row "J" untill a new name is inserted. Im not sure how to "pull up the cells" everytime a name is deleted so that your list box wont show any spaces. Hope this helps!!