Populate ListBox1 with rows that have expired dates in.

KyleJackMorrison

Board Regular
Joined
Dec 3, 2013
Messages
107
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
Hello,

I have a complex sheet that has hundreds of employee's on who have completed courses.

As each month i have to give a list of name to my boss about whose course has been expired (Courses last 2 years). I have the userform all set up, however i can't seem to get the code to search and populate the list box with only people who have been expired.

Here's what my sheet looks like:

TitleNameID No.Course 1
MrJoe2233445501/01/2017
MissMary1122334410/10/2014
MrSmith9988776601/01/2012

<tbody>
</tbody>

As you can see, i would like the listbox1 to display Miss Mary and Mr Smith as their courses have expired past 2 years.

Any help is much appreciated!

KJM
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
@Fluff,

appologise for the slow reply, was taking the little one out for halloween. :)
No problem, family always come first.


Thanks for the file, the problem was that you had the listbox columns set to 1
Give this a go
Code:
Private Sub UserForm_Initialize()

    Dim Cl As Range

    With ListBox1
        .ColumnCount = 4
        .ColumnWidths = "30;50;20;50"
    End With

    With CreateObject("scripting.dictionary")
        For Each Cl In Sheets("Unit 1").Range("D5", Sheets("Unit 1").Range("D" & Rows.Count).End(xlUp))
            If Cl.Value < DateAdd("yyyy", -2, Date) Then
                .Item(.Count) = Cl.Offset(, -3).Resize(, 4).Value
            End If
        Next Cl

        ListBox1.List = Application.Index(.Items, 0, 0)

    End With

End Sub
You may need to tweak the columnwidth settings
 
Upvote 0
@Fluff,

Outstanding, that's perfect.
Thank you for posting and helping me out with this!
Have a good evening!

Kind regards,
KJM
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
Give this a go
Code:
Private Sub UserForm_Initialize()

    Dim Cl As Range

    With ListBox1
        .ColumnCount = 4
        .ColumnWidths = "30;50;20;50"
    End With

    With CreateObject("scripting.dictionary")
        For Each Cl In Sheets("Unit 1").Range("D5", Sheets("Unit 1").Range("D" & Rows.Count).End(xlUp))
            If Cl.Value < DateAdd("yyyy", -2, Date) Then
                .Item(.Count) = Cl.Offset(, -3).Resize(, 4).Value
            End If
        Next Cl

        ListBox1.List = Application.Index(.Items, 0, 0)

    End With

End Sub
You may need to tweak the columnwidth settings
You, the OP and future readers of this thread may find it interesting that your UserForm_Initialize event code above can be compacted by removing the scripting dictionary and For..Next loop (although I will admit the code may be a tad hard to follow :devilish:)...
Code:
[table="width: 500"]
[tr]
	[td]Private Sub UserForm_Initialize()
  With ListBox1
    .ColumnCount = 4
    .ColumnWidths = "30;50;20;50"
    .List = Evaluate("{""" & Replace(Replace(Application.Trim(Join(Application.Transpose(Evaluate(Replace("IF(D5:D#< DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())),A5:A#&"""""",""""""&SUBSTITUTE(B5:B#,"" "",""|"")&"""""",""""""&C5:C#&"""""",""""""&TEXT(D5:D#,""mm/dd/yyyy""),"""")", "#", Cells(Rows.Count, "A").End(xlUp).Row))))), " ", """;"""), "|", " ") & """}")
  End With
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
@Fluff,

So, after yourself giving me that code it worked perfectly.

However i was foolish enough to try and put it in the true version of the sheet and then change the sheet and it's all gone wrong.

The expiry date, doesn't seem to pick up the date in the cell "E" when the data has been inputted via a userform textbox. However if you type in the date on the sheet it works. (apart from if there is only 1 date expired).

I was wondering if you could have a look at it and point me in the right direction.

https://www.dropbox.com/s/rzf1pjge1g...heet.xlsm?dl=0

Many thanks in advance.

KJM
 
Upvote 0
You need to change the code in the update form
Code:
        If CheckBox1.Value = True Then
            ActiveSheet.Cells(Intcandidate, 5) = CDate(TextBox1)
        Else
            ActiveSheet.Cells(Intcandidate, 5) = ""
        End If
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top