Vacation Time Error VBA Code

that_one_girl

New Member
Joined
Mar 22, 2017
Messages
43
Hello Everyone,

I currently have a worksheet that will populate days with the initials of the Staff that has been assigned a task that begins and ends in a certain time frame. I've used an IF function for this.

I now have another task I must do. My boss wants there to be an Error Message generated if the staff member is assigned a task during their vacation time.

Normally I would use data validation, but I don't think I can. I need help.

I'm trying to come up with a VB code that will check the begin date for the task against the begin date of the vacation, the end date of the task against the end date of the vacation, and then see if the staff member next to both the date frames matches - and if it does, given an error box message, and if not, then to just continue to follow the formulas entered.

EXAMPLE:

TaskSheet1 =
Begin Task 5/10/17
End Task 5/21/17
Staff ML
Populates these dates with ML using =IF(AND(Dates>=Begin,Dates<=Deadline),Staff,"") 'populates the date fields listed with their initials


VacationSheet2 =
VABegin 5/18/17
VAEnd 5/22/17
Staff ML
Need a VB Code to check and if true, Generate "ERROR: ML is on Vacation 5/10 - 5/21 please assign another staff member to this task."

If the dates do not over lap, then allow the assignment to continue..........
Any suggestions?
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

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
Back
Top