Pop up box - Route control

[KiloHotel]

Hi Guys,

I am looking to get a macro created for an excel sheet, I have tried searching on here and cannot come across anything.

I have a sheet that contains a list of all the departure times and assigned route number and amongst other details. Departure times is in A3 onwards to 400+ And Route is in E3 onwards.

What I am trying to achieve is firstly a popup to say check "route number" with options in a box to say all ok or there is an issue, I have 20-30 routes that need checking one after the other. However I want it to correlate with the current time of checking it. So if I get this message at 14:00 I want it to only affect departures in and around 14:00 with that route number so maybe a pop up of the past 30 min departures and future 30 mins, also If there is an issue I want to be able to put either free text in a box so it goes straight to a cell listed say "comment"


I have tried this; but it seems to be a one use case and you have to select a time, and I can’t figure it out.

Sub StartTimer()
Static SchedSave
If SchedSave <> 0 Then
Application.OnTime SchedSave, "TimeOver", , False
End If
'SchedSave = TimeValue("23/03/2021 07:27:30")
SchedSave = TimeValue("21/04/2021 15:41:00")
Application.OnTime SchedSave, "TimeOver", , True
SchedSave = TimeValue("21/04/2021 15:42:00")
Application.OnTime SchedSave, "TimeOver", , True
End Sub

Sub TimeOver()

MsgBox " Check route 1"
Call StartTimer

End Sub

I hope I made it clear

Let me know if there is more that I need to explain.

Thanks!

 

[vw412]

what information (or lack thereof) would trigger whether to include a route in the popup box? Should this process run continuously from workbook open to workbook close? Are you just needing to look at a moving time window centered on Now time? Will you be doing dataentry into the sheet while the process is running?

 

[KiloHotel]

Hi, the only trigger would be if there is no data entry to say the route had been checked in the timeframe of past 20 mins and future 20 mins say. so if it detects Route 1 for example as been checked in the last 20 mins then an alert would not be required until Route 1 comes around again in an hours time etc.

ideally it would be a continuous thing from workbook open to close as there will be multiple users opening it throughout the day.

regarding the moving time window, I think it’s the best option cause let’s say a user is away from the PC for 45 mins then there is no point in checking something that far back, best course of action is to have it check at that given moment or moving time.

The data entry i hope to have is when the box pops up to say if it’s checked and then an “IF” analyses when there is a problem to have free text to be inputted to that dedicated cell in line with the route number.