Adding reminders with msg including - note / date and time

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,087
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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,087
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,087
Hiya thanks for your reply. Do I change it to <code>If cell = Date Then </code>
Thanks again
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,305
Messages
5,836,539
Members
430,436
Latest member
Emilycr

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
Top