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

CaptainChaos

New Member
Joined
Jun 11, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • Excel Data Entry Form - Drop down required for Function.png
    Excel Data Entry Form - Drop down required for Function.png
    52.7 KB · Views: 20

CaptainChaos

New Member
Joined
Jun 11, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
Glad it all worked out. :)
This line of code identifies the target row:
VBA Code:
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

New Member
Joined
Jun 11, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Glad it all worked out. :)
This line of code identifies the target row:
VBA Code:
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.
 

Forum statistics

Threads
1,141,774
Messages
5,708,458
Members
421,570
Latest member
BaileyJ

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
Top