VBA code for Reminder in Excel

If you want to create any reminder in excel, then you can follow the vba code

Step1: define worksheet

Step 2: write code for Listbox headers

Step 3: describe a variable so that it can find every cell value

Step 4: now format the cells value into date and put a condition, if cell value is equal to today's date then add item into Listbox 1 from that row which you want to show in Listbox 1. Data will add in first column of Listbox 1.

Me.ListBox1.AddItem sh.Cells(i, 1) ' this is for Column No1, i.e Col A
  

Now add item on Listbox1's another column by

Me.ListBox1.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 5) 'for col no5 i.e col E

And so on. Listbox 1 will populate data as per current date.

Now we will discuss with Listbox 2 where we populate data for next 7 days. And We will set this userform while we open the file.

At the same way we develop the Listbox 2 and just we will change the condition for Listbox 2.

Listbox 1 has been populate as per below condition

If Format(sh.Cells(i, 5), "dd-mmm") = Format(Date, "dd-mmm") Then

And we will change it to

If Format(sh.Cells(x, 5), "dd-mmm") <= Format(Date + 7, "dd-mmm") And Format(sh.Cells(x, 5), "dd-mmm") > Format(Date, "dd-mmm") Then

That is date will capture greater than today and less than or equal to next 7 days.

I hope this tutorial will help you to understand the base idea about this module.

Thank you.

.................................................

 Option Explicit

Private Sub UserForm_Initialize()

'define worksheet in which worksheet you want to work

Dim sh As Worksheet

Set sh = Sheets("Sheet1")

'Listbox Headers

Me.ListBox1.AddItem "Student Name"

Me.ListBox1.List(0, 1) = "Date of Birth"

Me.ListBox1.List(0, 2) = "Phone No."

Me.ListBox1.List(0, 3) = "Class"

'To Select ListBox Headers

Me.ListBox1.Selected(0) = True

'Populate listBox under this condition

Dim i As Long

For i = 4 To sh.Range("A100000").End(xlUp).Row

If Format(sh.Cells(i, 5), "dd-mmm") = Format(Date, "dd-mmm") Then

Me.ListBox1.AddItem sh.Cells(i, 1) ' this is for Column No1, i.e Col A

Me.ListBox1.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 5) 'for col no5 i.e col E

Me.ListBox1.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 6) 'for col no6 i.e col F

Me.ListBox1.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 9) 'for col no9 i.e col I

End If

Next i

'.............................................................................

' Now for ListBox2

Me.ListBox2.AddItem "Student Name"

Me.ListBox2.List(0, 1) = "Date of Birth"

Me.ListBox2.List(0, 2) = "Phone No."

Me.ListBox2.List(0, 3) = "Class"

'To Select ListBox Headers

Me.ListBox2.Selected(0) = True

'Populate listBox under this condition

Dim x As Long

For x = 4 To sh.Range("A100000").End(xlUp).Row

If Format(sh.Cells(x, 5), "dd-mmm") <= Format(Date + 7, "dd-mmm") And Format(sh.Cells(x, 5), "dd-mmm") > Format(Date, "dd-mmm") Then

Me.ListBox2.AddItem sh.Cells(x, 1) ' this is for Column No1, i.e Col A

Me.ListBox2.List(ListBox2.ListCount - 1, 1) = sh.Cells(x, 5) 'for col no5 i.e col E

Me.ListBox2.List(ListBox2.ListCount - 1, 2) = sh.Cells(x, 6) 'for col no6 i.e col F

Me.ListBox2.List(ListBox2.ListCount - 1, 3) = sh.Cells(x, 9) 'for col no9 i.e col I

End If

Next x

End Sub