Using a row source for a listbox??
Using a row source for a listbox??
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Using a row source for a listbox??

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    ni****h desai
    nisht@pexcel.com
    you can download a example file 16 from

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



  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,803
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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?


  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sure, but what do you mean??

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,803
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Thanks

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,803
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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!!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com