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).

ADDITIONAL INFO:

Each platoon will have its own file. Within that file I need 14 schedules. The first schedule has the first start date and each of the 13 schedules after that will automatically be populated with the dates. This allows the Sgt/Cpl to enter vacation requests, training dates, etc. up to a year in advance.

The codes you see (101, 102,103,120 and ROV etc) are sector assignments (all equalling 12 hour work days). The other items you see in the values list are various codes for various functions:

Code
4/C = 4 hour duty day but using 4 hours of Comp instead of working
4/V = 4 hour duty day but using 4 hours of Vacation instead of working
4/X = 4 hour duty day. The X stands for Rec time (hours off/days off)
8/C = 8 hour duty day but using 4 hours of Comp instead of working
8/V = 8 hour duty day but using 4 hours of Vacation instead of working
8/X = 8 hour duty day
[C is an entry where I need to add a value. For instance if C is entered I need to enter how many C hours)]
C = Comp time
F/T = Field Training. The hours will match the FTO on the same schedule. So if someone is in F/T with C4, those hours will match. So ultimately I would like to enter F/T and have it ask "Who is the FTO for this date?" Then select the FTO from a list (low priority function)
H = Holiday (not used currently. Will eventually match a duty day (8 or 12 hours)
L/D (12) = Light duty - 12 hours
L/D (4) = Light duty - 4 hours
L/D (8) = Light duty - 8 hours
M/L = Military Leave (12 hours)
M/L (4) = Military Leave (4 hours)
M/L (8) = Military Leave (8 hours)
M/L/C = Military Leave (12 hours COMP) This occurs when military leave time has been depleted
M/L/C (4)= Military Leave (4 hours COMP) This occurs when military leave time has been depleted
M/L/C (8)= Military Leave (8 hours COMP) This occurs when military leave time has been depleted
M/L/V = Military Leave (12 hours VAC) This occurs when military leave time has been depleted
M/L/V (4)= Military Leave (4 hours VAC) This occurs when military leave time has been depleted
M/L/V (8)= Military Leave (8 hours VAC) This occurs when military leave time has been depleted
O = Other (12 hours)
O (4.00) = Other (12 hours)
O (8.00) = Other (12 hours)
ROV = Rove unit (12 hours)
[S/L is an example of wanting to enter "S/L" and being prompted for x amount of hours]
S/L = Sick Leave (12 hours)
S/L (4) = Sick Leave (4 hours)
S/L (8) = Sick Leave (8 hours
S/T = School/Training (12 hours)
S/T (4) = School/Training (4 hours)
S/T (8) = School/Training (8 hours)
S/T/C (4)= School/Training (4 hours worked, 8 hours COMP)
S/T/C (8)= School/Training (8 hours worked, 4 hours COMP)
S/T/V (4)= School/Training (4 hours worked, 8 hours VAC)
S/T/V (8)= School/Training (8 hours worked, 4 hours VAC)
TEST
V = VAC (12 hours)
V (4)/X = Taking 4 hours vacation in conjunction with a 8 hours REC
V (8)/X = Taking 8 hours vacation in conjunction with a 4 hours REC
X = REC day. No value

The only major constraint is that officer's are not supposed to exceed 160 hours, however, sometimes it happens based on manpower issues, emergency call outs, etc.

Another constraint is that we have to have a minimum manpower of 5. Anything less than that will be flagged (probably conditional formatting.

I hope this helps. I have eventual goals of tying other departments into these schedules. For instance, admin has to maintain a dry erase board of who is working and who is not. I was going to link a daily schedule file to all four of the platoon schedules to show them who is working on one page that can be printed.

This really needs to be a hosted web interface with a SQL table driving it. I just don't have the skill set to build it. And to get the command staff to come off of Word schedules to uniform the document and add some automation was a monumental task to put it lightly.