View Full Version : [SOLVED:] Calendar pop up reminder
CuriousGeorg
11-19-2013, 07:59 AM
Good afternoon,
I'm looking for a simple query. I need a calendar that when a particular item is due within the next 7 days a pop up appears showing what is due and what dates.
This can be as simple as a list of:
column a: What Due
column b: Date Due
and the pop up just shows both. (ideally the pop up is a UF)..
unfortunately, conditional formatting isnt worth it here and i cant use outlook or add ins.
if i could have a calendar that has a similar outcome then great.
(its basically going to be a holiday tracker that pops up on open who is off in the next 7 days)
Any help would be great.
Bob Phillips
11-20-2013, 04:10 AM
Public Function DueTasks()
Dim msg As String
Dim lastrow As Long
Dim i As Long
With Worksheets("Sheet2")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
If .Cells(i, "B").Value >= Date And .Cells(i, "B").Value < Date + 7 Then
msg = msg & vbTab & .Cells(i, "A").Value & " due on " & Format(.Cells(i, "B").Value, "ddd dd-mmm") & vbNewLine
End If
Next i
If msg <> "" Then
MsgBox "Items due in next 7 days:" & vbNewLine & msg, vbOKOnly, "Due Tasks"
Else
MsgBox "Nothing due in next 7 days", vbOKOnly, "Due Tasks"
End If
End With
End Function
CuriousGeorg
11-20-2013, 06:32 AM
ok that works nice.
I just need to have it pop up as soon as it loads.. Sweet. :)
Bob Phillips
11-20-2013, 07:15 AM
Put it in the Workbook_Open event.
CuriousGeorg
11-21-2013, 05:56 AM
xld Thanks for this it works a treat I just have a slight problem..
If there is a date due it produces the pop up 12 times..
Code im using is
Private Sub workbook_open() Dim msg As String
Dim lastrow As Long
Dim i As Long
With Worksheets("Jan")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow
If .Cells(i, "A").Value >= Date And .Cells(i, "A").Value < Date + 7 Then
msg = msg & vbTab & .Cells(i, "B").Value & " off on " & Format(.Cells(i, "A").Value, "ddd dd mmm") & vbNewLine
End If
Next i
If msg <> "" Then
MsgBox "Leaders off within the next 7 days:" & vbNewLine & vbNewLine & msg, vbOKOnly, "Who"
'Else
'MsgBox "No-one off within next 7 days", vbOKOnly, "Leader Absence"
End If
End With
With Worksheets("Feb")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow
If .Cells(i, "A").Value >= Date And .Cells(i, "A").Value < Date + 7 Then
msg = msg & vbTab & .Cells(i, "B").Value & " off on " & Format(.Cells(i, "A").Value, "ddd dd mmm") & vbNewLine
End If
Next i
If msg <> "" Then
MsgBox "Leaders off within the next 7 days:" & vbNewLine & vbNewLine & msg, vbOKOnly, "Who"
'Else
'MsgBox "No-one off within next 7 days", vbOKOnly, "Leader Absence"
End If
End With
With Worksheets("Mar")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow
If .Cells(i, "A").Value >= Date And .Cells(i, "A").Value < Date + 7 Then
msg = msg & vbTab & .Cells(i, "B").Value & " off on " & Format(.Cells(i, "A").Value, "ddd dd mmm") & vbNewLine
End If
Next i
If msg <> "" Then
MsgBox "Leaders off within the next 7 days:" & vbNewLine & vbNewLine & msg, vbOKOnly, "Who"
'Else
'MsgBox "No-one off within next 7 days", vbOKOnly, "Leader Absence"
End If
End With
With Worksheets("Apr")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow
If .Cells(i, "A").Value >= Date And .Cells(i, "A").Value < Date + 7 Then
msg = msg & vbTab & .Cells(i, "B").Value & " off on " & Format(.Cells(i, "A").Value, "ddd dd mmm") & vbNewLine
End If
Next i
If msg <> "" Then
MsgBox "Leaders off within the next 7 days:" & vbNewLine & vbNewLine & msg, vbOKOnly, "Who"
'Else
'MsgBox "No-one off within next 7 days", vbOKOnly, "Leader Absence"
End If
End With
End Sub
ETC ETC
So, whilst it picks up the correct month. It just shows the pop up several times and i'm not sure why.. can you shed any light.
Bob Phillips
11-21-2013, 08:41 AM
SO you have a separate sheet for each month? Whilst I wouldn't do that, it shouldn't cause a problem :bug:. But you also should not check each sheet that is wasteful, the next 7 days can only be in this month and next, so you should only check those. I would also keep appending to one message, not throw out two at month-end.
All of this helps you not a jot, and I am afraid I don't see anything in the code to cause it. I just tried it and only got one message. Yiu don't have all dates in each sheet do you?
CuriousGeorg
11-21-2013, 08:59 AM
Thereason the months are on separate sheets is there is plenty more as a planner in the sheet and it's the best way to organise everything. The dates "off" for January are in January's sheet. February's in February etc. I guess I could have a summary page of all the dates and get the,code to read from that. Perhaps it's just me. Thanks anyway
Bob Phillips
11-22-2013, 06:04 AM
I know what it is, you need to initialise msg before every month test, just use
msg = ""
CuriousGeorg
11-22-2013, 06:26 AM
Sorry confused where to put that. I ha've msg in the code. Not at a computer to play around but I can't see where it goes by eyeballing. Sorry if I sound an idiot..
Edit.. I see it
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.