Prompt/Store value based on a selection/entry

Prompt/Store value based on a selection/entry
Greetings.

I have created a new schedule for the police department I work at. I had to replicate the old schedule, which was in table format in Word so I was somewhat limited.

The redesign included the following changes:


Restricting entry to make all the schedules (4 Platoons) uniform
To make the color formatting uniform (using conditional formatting)
To add automated calculations to make sure officers fall within 160 hours, as well as total Comp, Vacation, etc. for our payroll coordinator.
The ability to populate 14 schedules in one Excel file as compared to 14 Word schedules for each platoon x4.



My ultimate goal is to tie in our 28 day sheets to read, and populate them automatically based on the schedule.

(Yes. I know this application would be better served if it were web based front end with a SQL db, or even an Access db, but the command staff does not want to waste the money on such 'nonsense' so I had to stay in Excel)

My current problem is with Vacation Days (V), Sick Leave (S/L), and Comp (C). There are several different hours that can be associated with these days. Most everything is based on 12, 8, or 4 hours, but let's say I have a doctors appointment that only takes me off the road for 2 1/2 hours. Then I need to be able to enter 2.5. My drop downs are based on a value list. To add S/L 1, S/L 2, etc. will make the list even more congested than it already is.

Is there a way to use VB to look at the entry and if the user enters S/L from the drop down, a window pops up and asks "How many hours of S/L would you like to enter?" Then the user enters the hours which is stored in the field. I have been searching and tried many options but I just can't seem to get it and we are trying to get the new forms in play.

If anyone could help me, I really would appreciate it. Thank you for your time.

Also, if you see a better design please let me know. I know my way around Excel but I am obviously no master. There are a lot of formulas based on hidden columns, as you will see.

Additionally, I was wondering if there was a way to send an e mail to the commanders when the schedule is changed (saved).