VBA Get the cell value in an active column when an entry is made in a different cell

[Moonie]

Hello all,
Long time reader, first time poster...I'm new to VBA and have been learning as I go (most of it here thanks to you all). I have a monthly schedule that has all 3 shifts. When I enter an employees type of leave into a cell I have a message box pop up and ask if this creates overtime. If I select yes it automatically opens up the overtime sign up sheet. Is there a way to have the code also select the cell in row 1 of the same (active?) column, which is the date the occurrence takes (or will take) place, from the monthly schedule, and copy that cell. Then after it opens up the overtime sign up sheet place that date in cell A16? I tried to upload my work but it is too large. Any help would be greatly appreciated. Here is the code that I have so far:

Please Login or Register  to view this content.

[Moonie]

bump....no response

Any help would be greatly appeciated.

[Chippy]

Try something like this:

Please Login or Register  to view this content.

This assumes that, as your code implies, each type of leave updates the same overtime workbook (the first sheet in that workbook). For Excel 2010, you will need to change the .xls file extension in the code to the one used by your workbook, e.g. .xlsx, xlsm, etc.

[Moonie]

Thank you Chippy this works out great!

Edit:

I added to the code to break it down by shifts and add some variables depending on if it leaves the shift below our allowable minimum. This basically fills out the OT form completely for us adding the date and time that needs to be covered. Thanks again Chippy!

Please Login or Register  to view this content.

[Chippy]

A small bug fix:

Please Login or Register  to view this content.

Make this change to leaveTypes string and the If statement in the 3 places where it is used.

[Moonie]

Thanks again Chippy. I found that since the schedule is split into three shifts, the msg box had to pop up twice if the shift minimum was met in two shifts in the same column (sorry I'm still learning and this should of been obvious). Had I been able to upload the Workbook (too large) then I'm sure you would of been able to see what I was trying to accomplish much faster. Below is the final code that you helped me get to. Again a big thank you.

Please Login or Register  to view this content.