Adding reminders with msg including - note / date and time

[Patriot2879]

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?

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

 

[GR00007]

Does this work?

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]

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]

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.

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

 

[Patriot2879]

Hiya thanks for your reply. Do I change it to <code>If cell = Date Then </code>
Thanks again

 

[Patriot2879]

brilliant thankyou that works great, and thanks for the advise