force drop-down list only when 1 of 6 possible answers in a different cell is met.

[CaptainChaos]

I have done some reading on dependent drop down lists, but everything I have found refers to when there are for example 2 or more choices in 1 drop-down cell, and whichever answer is selected forces another drop-down menu. My problem is similar to that but not identical.
In my situation, I need only to force a drop-down menu if 1 of 5 possible answers is true.
cell F8 has a drop-down menu showing choices as follows:
Crew,
Services,
Catering,
Operator,
Operator Services,
Other

The cell below (F9) asks for the function or Job title related to the entry made in F8. If any choice is made other than "Crew", then a drop-down is not required, and any value can be placed in the cell. However, if "Crew" was selected in F8, then a drop-down is required to ensure that only certain jobs are selected to ensure the spelling of the job is consistent to link it to a pay-roll sheet in the same workbook.

How do I ensure that a drop-down list of job-titles (EG: Electrician, ASST Electrician, Mechanic etc) is forced in CELL F9 if Crew is selected in CELL F8, but allows any value to be put in F9 if anything other than "Crew" is selected in F8.

Hope that is clear.

Any assistance would be appreciated.

 

[CaptainChaos]

You are very welcome.

Mumps.

My statement yesterday that all was working perfectly was premature. I hadn't at that point submitted data. I was just so ecstatic to see that I could switch between selecting Rig Crew and gtting a drop-down, and something else and not getting a drop-down. Once I started submitting the data or using the search macros I stared getting a message box "you must enter a category", despite the fact a category was already entered. This was cleared however once OI was clicked, but then I was getting a run-time error once the process was completed.


In order to get it to work, I had to delete the following part of your code:

Case Is = 9
If Range("F8") = "" Then
MsgBox ("Please select a CATEGORY first.")
Range("F9").ClearContents
Range("F8").Select
End If

I also had to delete the following code from the validation sub
'Turn conditional formatting for errors off.
showErrorCell.Value = 0

End If

'Protect the Worksheet
ActiveSheet.Protect "123456"

Now that those changes have been made, I am delighted with the fact that it works. However, one of the reasons I try very hard to do as much of any excel work as I can myself, is I need to understand the code so that if anything does go wrong I can fix it.

Can you explain to me what the "Case is 8" refers to in the code ?

I'm not sure what "Case 9" was either, but that part was deleted to make it compatible with the existing code.
Grateful if you could find a little bit of time to explain how it works so that I can get my head around it in the event I need to change things in the future.

Thanks again. I'm making great strides now linking the spreadsheet to the Payroll, something that this time last week was causing me a huge headache.

 

[mumps]

Glad it all worked out.
This line of code identifies the target row:

Select Case Target.Row

"Case 8" says that if the target row is 8 then execute the code below that line.
"Case 9" says that if the target row is 9 then execute the code below that line.

I hope this helps.

 

[CaptainChaos]

Glad it all worked out.
This line of code identifies the target row:

Select Case Target.Row

"Case 8" says that if the target row is 8 then execute the code below that line.
"Case 9" says that if the target row is 9 then execute the code below that line.

I hope this helps.

Thanks. Appreciate it.

 

[mumps]

My pleasure.