Results 1 to 5 of 5

Thread: Solved: Populate Listbox with Mulitple Columns

  1. #1
    VBAX Contributor
    Nov 2007

    Solved: Populate Listbox with Mulitple Columns

    Hey again,

    Listbox3 located in Sheet("Reports")
    Core data located in Sheets("Workload")

    Depending on a value (staffmember) selected in Listbox1, I am looking for code that will search for this value in Sheets("Workload") - Column G

    Sheets("Workload") - Row 3 has all my headings. Columns B - H are just categories while Column I onwards is week-ending dates.

    Once the staff member is found in Column G of Sheets("Workload") I need the value from Columns C (Project name) added to listbox3

    As for the week-ending dates, I need 6 columns of data(first column being the current week-ending)

    So the end result should be 7 columns of data (1 name and 6 values)

    Another twist is that there may be more than one entry for Staffmember but the Project Names are uniqe. So the code would have to list a row of name and 6 values for each Project Name found for that particular staffmember.

    Here is some code I have noted down that would probably need to go in it.

    Option Explicit

    Function ColumnLetter(col As Long)
    Dim sColumn As String
    On Error Resume Next
    sColumn = Split(Columns(col).Address(, False), ":")(1)
    On Error GoTo 0
    ColumnLetter = sColumn
    End Function

    Sub Add_Info_to_Listbox3()

    'This code is not any particular order'
    'I'm just noting down what needs doing

    'Current Week ending date
    StartDate = Date + 6 - Weekday(Date)

    'Find weekending value in Row 3 of Sheet("Workload")
    StartCol = Application.Match(CLng(Start2Date), Worksheets("Workload").Rows(3), 0)

    'Find staff member in Column G of Sheets("Workload")
    temp = ListBox1.ListIndex
    With ListBox1
    StaffMember = .List(temp, 1) & " " & .List(temp, 0)
    End With
    On Error Resume Next
    Rw = Sheets("Workload").Columns(7).Find(StaffMember).Row
    'Code to find and select 7 columns of data here
    'and add to listbox 3
    End Sub

    Example sheet attached in case none of the above made sense

  2. #2
    VBAX Master Norie's Avatar
    Jan 2005
    Stirling, Scotland
    There's some problems with the attachment.

    Whenever I try to do anything I'm told a library or reference is missing and Date is highlighted.

    I'm then prompted for a password.

    And if I goto Tools>References... I also get prompted for a password.

  3. #3
    VBAX Expert
    Aug 2007
    Windermere, FL, a 'burb in the greater Orlando metro area.
    <entry deleted by RonMcK>
    Windermere, FL

  4. #4
    VBAX Contributor
    Nov 2007
    If you are asked for a password, try this:


    Plus for some weird reason, now when i open this program on other computers, I get a compile error and the word 'Date' is highlighted saying could not find library, etc.
    Last edited by f2e4; 06-11-2008 at 12:03 PM.

  5. #5
    VBAX Contributor
    Nov 2007
    If it would make it any easier:

    I originally had a pivot table of sheet("Workload") and used the the values in in as a Dynamic Named Range for the listbox.

    This worked perfectly up to a point.

    The reason I'm looking at multiple columns now is that I couldn't get the datafield of the pivottable to change to current week ending value - it always remained at the value that i had 'maunally' selected to set it up in the first place.

    If the listbox code above is too much hassle, does anyone know how I can change the datafields value to the current weekending value

    This is the pivotcode i used:

    Created new topic for this

Posting Permissions

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