Adding reminders with msg including - note / date and time

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hi I have the code blow where popup message appears with info from row B and C, row B has the message and row C has the date, I would like to add a row D with the time (24 hour format like 13:54), please can you advise how I would add this to the code below? I hope you can help please?


Code:
Private Sub Workbook_Open()
Dim cell As Range, buf As String

With Sheets("Sheet1")
    For Each cell In .Range("C:C").SpecialCells(xlConstants)
        If cell >= Date - 1 And cell <= Date + 3 Then
            buf = buf & vbNewLine & Format(cell.Value, "    M/D/YYYY - ") & cell.Offset(, -1).Value
        End If
    Next cell
    If Len(buf) > 0 Then MsgBox "Hi Hiren," & vbNewLine & "Today is " & Date & " and the item(s) due are:" & vbLf & buf
End With
End Sub
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
Does this work?
Code:
Private Sub Workbook_Open()
Dim cell As Range, buf As String


With Sheets("Sheet1")
    For Each cell In .Range("C:C").SpecialCells(xlConstants)
        If cell >= Date - 1 And cell <= Date + 3 Then
            buf = buf & vbNewLine & Format(cell.Value, "    M/D/YYYY - ") & cell.Offset(, -1).Value & " @ " & Format(cell.Offset(, 1).Value, "hh:mm")
        End If
    Next cell
    If Len(buf) > 0 Then
        MsgBox "Hi Hiren," & vbNewLine & "Today is " & Date & " and the item(s) due are:" & vbLf & buf
    End If
End With
End Sub
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hello, good morning, thank you for your help, this does work, the only thing now is that it doesn't bring up the reminders for just on day, it brings up previous reminders as well. Is there anyway it can bring up the reminders for on day only?
 

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
That code was bringing up cells from yesterday (>= Date -1) thru to the third day (<= Date +3).
Just change
>= Date -1 And cell <= Date + 3
to
= Date.

Note: if you want to keep the line of code for reference, just put an apostrophe in front of it to comment it out.
Code:
Private Sub Workbook_Open()
Dim cell As Range, buf As String
With Sheets("Sheet1")
    For Each cell In .Range("C:C").SpecialCells(xlConstants)
        'If cell >= Date - 1 And cell <= Date + 3 Then
        If cell = Date Then
            buf = buf & vbNewLine & Format(cell.Value, "    M/D/YYYY - ") & cell.Offset(, -1).Value & " @ " & Format(cell.Offset(, 1).Value, "hh:mm")
        End If
    Next cell
    If Len(buf) > 0 Then
        MsgBox "Hi Hiren," & vbNewLine & "Today is " & Date & " and the item(s) due are:" & vbLf & buf
    End If
End With
End Sub
 
Last edited:

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hiya thanks for your reply. Do I change it to <code>If cell = Date Then </code>
Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,280
Members
409,814
Latest member
Leon_Al

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top