Solved: Populate Listbox with Mulitple Columns

[f2e4]

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.

[vba]
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
'
'
'
'
Sheets("Reports").ListBox1.Activate
End Sub
[/vba]


Example sheet attached in case none of the above made sense

[Norie]

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.

[RonMcK]

<entry deleted by RonMcK>

[f2e4]

If you are asked for a password, try this:

21544

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.

[f2e4]

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