Need help with Listbox

[Guest]

Hi Everyone,

In my spreadsheet, I have the following choices in G4 to G54.
Not Started
In Progress
Completed

And from H4 to H54, It contains the date when the tasks should be completed.
e.g. 7/27/2005
8/15/2005

From C4 to C54, I have a list of tasks.


I have added a form and 2 listboxes. On the first listbox, i want to show
all the tasks overdue, and on the 2nd listbox i want to show all the tasks
that are still on schedule. And i dont know how to do it.

To determine if the task is overdue, the status is not started, in progress
and exceeds to the date of completion otherwise it should fall on listbox2.


Please help.

Thanks in advance

 

[Bob Phillips]

You should use the advanced filter to create separate ranges of data that
match your criteria, and then link the listbox to these ranges.

 

[Dave Peterson]

I put two listboxes and a button on a userform and put this behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("g4", .Cells(.Rows.Count, "G").End(xlUp))
End With

For Each myCell In myRng.Cells
If myCell.Offset(0, 1).Value >= Date Then
'do nothing, not due/overdue
Else
If LCase(myCell.Value) = "completed" Then
Me.ListBox1.AddItem myCell.Offset(0, -4).Value
Else
Me.ListBox2.AddItem myCell.Offset(0, -4).Value
End If
End If
Next myCell
End Sub

========
Although, I think I would take a different approach.

I'd use a helper column of cells:
=IF(G4="completed","",IF(H4>=TODAY(),"","Overdue"))
and drag down the data

Then I could apply Data|Filter|Autofilter and see lots of stuff all at once
(instead of through the "eyes" of a userform's listbox.

ps.

I wasn't sure what happened on today. If it's due today, is it overdue or just
ontime.

Change the >= to what you want (in both the code and formula).

 

[Guest]

Thanks Dave! The code is very helpful. You're great.