How do I create a Pop-up Notification/Reminder in Excel?

[excelnewbie999]

Hello everyone,

I'm hoping someone can help me with a bit of dilemma. I have a long list of cases that I am working on. I have to be reminded on which ones to chase everyday. I have used conditional formatting where certain cases are coloured which kind of helps, but as the list gets longer, it's difficult to keep up with the coloured cells, , especially when my vision is weak.

Therefore I feel the best way forward is to have Excel show a notification/reminder that pop-ups based on a date that I have entered. I know this requires a VB script, but not sure how to do this. I'd be very grateful if you could assist me. Please see the below example spreadsheet where cases 1,3,5 show a pop-up in Excel. Any questions, please ask away. Thank you.

Example of Reminder Spreadsheet.xlsx
	B
5	Case Number
Sheet1

 

[Joe4]

Welcome to the Board!

Why not just use Filters, and filter the record with today's date?
If you are using Excel 365, you can use the new dynamic Filter function, and on another tab or in a different location in your sheet, you could have it dynamically return the list of records with today's date.

 

[Herakles]

Hello everyone,

I'm hoping someone can help me with a bit of dilemma. I have a long list of cases that I am working on. I have to be reminded on which ones to chase everyday. I have used conditional formatting where certain cases are coloured which kind of helps, but as the list gets longer, it's difficult to keep up with the coloured cells, , especially when my vision is weak.

Therefore I feel the best way forward is to have Excel show a notification/reminder that pop-ups based on a date that I have entered. I know this requires a VB script, but not sure how to do this. I'd be very grateful if you could assist me. Please see the below example spreadsheet where cases 1,3,5 show a pop-up in Excel. Any questions, please ask away. Thank you.

Example of Reminder Spreadsheet.xlsx
	B
5	Case Number
Sheet1

I was just about to say the same as in Post 2 but have a filter on the list as you have shown and then you can mark the reminder as having been actioned in another column and then have a button to refresh the list.

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

https://www.excelforum.com/excel-programming-vba-macros/1411338-how-do-i-create-a-pop-up-notification-reminder-in-excel.html

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[excelnewbie999]

Hi everyone and thank you very much for your replies.

@Joe4 and @Herakles - The problem with filtering is that I need to see all my cases and if I start filtering each day, then that seems a bit of an inconvenience and there is a chance that I may need to work on other claims too. Therefore for me personally, I require a pop-up reminder so that as soon as I open the spreadsheet, the cases that need following up for that day pop-up and I can then deal with them as a priority. Hope that makes sense.

@RoryA - Thanks and yes, I have posted the same question on the other forum. My apologies as I didn't know that the two forums are run by the same moderators. Should I only post on one of them?

 

[Herakles]

Hi everyone and thank you very much for your replies.

@Joe4 and @Herakles - The problem with filtering is that I need to see all my cases and if I start filtering each day, then that seems a bit of an inconvenience and there is a chance that I may need to work on other claims too. Therefore for me personally, I require a pop-up reminder so that as soon as I open the spreadsheet, the cases that need following up for that day pop-up and I can then deal with them as a priority. Hope that makes sense.

@RoryA - Thanks and yes, I have posted the same question on the other forum. My apologies as I didn't know that the two forums are run by the same moderators. Should I only post on one of them?

The idea of the filter is that you can deal with the cases that need following up for a day as a priority.

You can flip between these cases and all cases as and when you like.

It is what I have set up for many people for years and it works.

SO if you get a pop-up message that gives you a list you will either have to remember which ones they are or write them down and then go and find those cases to carry out the
follow up and then update the row.

 

[Jaafar Tribak]

The problem with filtering is that I need to see all my cases and if I start filtering each day, then that seems a bit of an inconvenience and there is a chance that I may need to work on other claims too. Therefore for me personally, I require a pop-up reminder so that as soon as I open the spreadsheet, the cases that need following up for that day pop-up and I can then deal with them as a priority. Hope that makes sense.

If the list of cases to chase gets too long , I would dump it somewhere like in the immdiate window or in a range after having notified the user with the MsgBox. This is so that you can refer back to the list at your convenience.

Try this :

In the ThisWorkbook Module:

Option Explicit

Private Sub Workbook_Open()
    'Assumes target sheet is *Sheet1* - change sheet as required.
    Call NotifyUser(Sheet1)
End Sub

Private Sub NotifyUser(ByVal Sh As Worksheet)
    Dim vData() As Variant
    Dim arFiltered() As String
    Dim sNotification As String
    Dim lLastRow As Long, UpperBound As Long, i As Long
  
    lLastRow = Sh.Columns("E").Cells(Sh.Rows.Count, 1&).End(xlUp).Row
    vData = Application.Transpose(Evaluate("""|""&" & "INDEX((E6:E" & lLastRow & ")=B2,)*ROW(E6:E" & lLastRow & ")" & "&""|"""))
    arFiltered = Filter(vData, "|0|", False)
    UpperBound = UBound(arFiltered)
    If UpperBound <> -1& Then
        For i = 0& To UpperBound
            sNotification = sNotification & Replace(arFiltered(i), "|", "") - 5& & IIf(i = UpperBound, "", " - ")
        Next i
        MsgBox "Following case number(s) need chasing today !! " & vbCrLf & vbCrLf & sNotification, vbExclamation
    End If
End Sub

 

[excelnewbie999]

If the list of cases to chase gets too long , I would dump it somewhere like in the immdiate window or in a range after having notified the user with the MsgBox. This is so that you can refer back to the list at your convenience.

Try this :

In the ThisWorkbook Module:

Option Explicit

Private Sub Workbook_Open()
    'Assumes target sheet is *Sheet1* - change sheet as required.
    Call NotifyUser(Sheet1)
End Sub

Private Sub NotifyUser(ByVal Sh As Worksheet)
    Dim vData() As Variant
    Dim arFiltered() As String
    Dim sNotification As String
    Dim lLastRow As Long, UpperBound As Long, i As Long
 
    lLastRow = Sh.Columns("E").Cells(Sh.Rows.Count, 1&).End(xlUp).Row
    vData = Application.Transpose(Evaluate("""|""&" & "INDEX((E6:E" & lLastRow & ")=B2,)*ROW(E6:E" & lLastRow & ")" & "&""|"""))
    arFiltered = Filter(vData, "|0|", False)
    UpperBound = UBound(arFiltered)
    If UpperBound <> -1& Then
        For i = 0& To UpperBound
            sNotification = sNotification & Replace(arFiltered(i), "|", "") - 5& & IIf(i = UpperBound, "", " - ")
        Next i
        MsgBox "Following case number(s) need chasing today !! " & vbCrLf & vbCrLf & sNotification, vbExclamation
    End If
End Sub

Hi Jaafar. Thank you so much for the coding. Apologies, but ither I'm doing something wrong or there maybe an error in the coding. I've placed your code in the spreadsheet, but I'm not getting any result. Could you ever so kindly check the screen shots below? Also if it helps, I simply want a pop-up to appear as soon as the spreadsheet loads, advising me of what case numbers need chasing.

Thank you again

 

[Jaafar Tribak]

I've placed your code in the spreadsheet, but I'm not getting any result.

You should place it in the ThisWorkbook Module

The code should execute each time the workbook is opend.

 

[excelnewbie999]

I'm once again really sorry Jaafar - I have already tried this, but it's not working. Being a newbie, I'm not sure what to do. Do I need to force Excel to run a macro or something?