VBA to check date and time and show message box

[Eclektics]

Hi peeps,

This is my first time writing on an Excel message board so I'll try and be clear. The company I work for has timescales for one of our processes, these timescales vary depending on the day and time.

e.g If an employee used this process Monday to Friday between 7am and 4pm, the timescales would be tomorrow after 10am. However, after 4pm the timescale would be 2 days in the future.

I want to write a VBA code for the work sheet to show this. I've included a drop down and when the employee selects the relevent criteria, depending on the date and time on their computer, a message box would appear and confirm the correct timescales.

Any help would be brilliant and if you need any more info let me know.

Thanks

 

[steve the fish]

Hi. Can the due date be a weekend?

 

[Eclektics]

Yes, there would be a specific timescale for Saturdays aswell. I can attach the full timescales if that would be easier?

 

[steve the fish]

This may help you along:

If Time > "16:00:00" Then
    myDate = Date + 2
Else
    myDate = Date + 1
End If

MsgBox "Due " & myDate & " after 10"

 

[Eclektics]

ADVERTISEMENT

Hi Steve,

Here are the timescales I'm working with. As you can see there are 2 statuses which don't make things easier. I've also included some code I started using further down, which works great for the time frame aspect but I'm unable to incorporate any date coding with it.

NON-URGENT
Monday to Friday: 7am - 4pm Timescale: Next day after 10am
Monday to Friday: 4:01pm - 6:59am Timescale: 2 days
Saturday: 7am - 4pm - Timescale: 2 days
Saturday to Monday - 4:01pm - 6:59am Timescale: Following Tuesday

URGENT
Monday to Friday: 7am - 4pm Timescale: 3 hours
Monday to Friday: 4:01pm - 6:59am Timescale: Next day after 10am
Saturday: 7am - 4pm Timescale: 3 hours
Saturday to Monday - 4:01pm - 6:59am Timescale: 10am on Monday

If Time >= #7:00:00 AM# And Time < #4:00:00 PM# Then
MsgBox ("Message will land next day after 10am"), vbOKOnly, _
"Message Timescales!"
Exit Sub
End If

Thanks

 

[steve the fish]

How are you telling the machine whether its urgent or not urgent? Do you already have something in place for this?

 

[Eclektics]

This will be done using dat validation on the worksheet. It will default to 'please select an option" and urgent and non-urgent will be the 2 available choices.

 

[Eclektics]

This will be done using dat validation on the worksheet. It will default to 'please select an option" and urgent and non-urgent will be the 2 available choices.