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
Mumps.
Thank you.
I am doing 12 hrs on / off shifts at the moment and am off shift with no access to a computer. However, thank you for your solution which I will try to find time to look at tomorrow.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

CaptainChaos

New Member
Joined
Jun 11, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Mumps.
To create the input form to have VBA input it to a datasheet, I have, I followed a TeachExcel.com tutorial on Youtube. The way the instructor demonstrated how the code should be written was to do all the data-validation on the excel sheet, and then call a different module (Call_Store_update_Data).

There are Several modules to the sheet.
One that validates the data.
One that stores the data on a separate data-sheet (Database)
One that Searches for existing data to be able to change it
One that either stores or updates - new stores on next empty row, or updates in the position the data was found.

Therefore, unless I am not sure at which point in the VBA modules I should paste the code you suggested.

My understanding is that all the data-validation is presently done on the input sheet first.

To better describe what I am talking about,, I will show the formulas in each of the cells in columns I, J, L and M
(M does not have coding - only a 0 or a 1 - by default set to 0)

Cell I4 - =AND(LEN(F4)>3,ISNUMBER(F4))

Cell I5 - =AND(LEN(F5)>0,LEN(F5)<50)

CELL I6 - =LEN(F6)>0 - Also has date Data Validation set up in F6 as allow Data - Data Grater than - Start Date 01/01/1950 (Any date below 1st Jan 1950 not valid)

CELL I7 - =LEN(F7)>0 - Drop down Data Validation list set up for F7. The drop down list - does not refer to cells, but have selected Data Validation list and allowed valid values of 1,2,3,4

CELL I8 - =LEN(F8)>0 - Also Drop down list in F8 which refers to List sheet Cells C2 to C7 (Crew, Services etc)

CELL I9 - =AND(LEN(F9)>0,LEN(F9)<50) - At the Moment I have a drop-down in cell F9 referring to the "List" Sheet cells A2 to A28 (Crew job titles), but I only want this to be referred to if "Crew" is selected in F8

CELL I10 - =LEN(F10)>0 - Data validation in Cell F10 set to Date Greater than 11/06/21 (periodically change date as time passes)

CELL I11 - =LEN(F11)>0 - Data Validation in Cell F11 - Same parameters as F10

CELL I12 - =LEN(F12)>0 - Data Validation in cell F12 set to greater than a recent date (11/06/21) - periodically changed.

CELL I13 - =LEN(F13)=4 - Data Validation in cell F13 refers to "List Sheet" cells G2 to G149 (Table name = Cabins_Table)

CELL I14 - = =LEN(F14)>0 - Dropdown list in cell F14 - does not refer to cells, but valid values listed as 1,2, 3

CELL I15 - =AND(LEN(F15)>0,LEN(F15)<50)

CELL I16 - =LEN(F16)>0 Data Validation drop-down in cell F16 - does not refer to cells but list offers options as D, N, UNK

CELL F17 - =AND(LEN(F17)>1,LEN(F17)<4)

The only remaining cell which has any sort of validation is a drop-down in cell F20 which allows either a 1 or a blank to be entered. Cells F18, F22, and F24 do not require any validation.

Column J (formatting) is used to conditionally format the cells in Column F if the validation in Column I returns "False"

Cell J4 reads as follows =AND(NOT(I4),$L$4) That formula is pulled down to cell F17

cell L4 is a cell to show the number of errors. Formula as follows:
Referred to in VBA module as Errorcount: - (Sub validate-Form() refers to Errorcount - Range (L4").Value



Cell M4 (Show Error) is set to 0
reference on vba Sub validate_form reads:
Set showErrorCell = Range (M4)
When it checks for errors
IF errorcount >0 Then
MsgBox errorcount & " Error(s)"
ShowErrorcell.value = 1

If there are no errors, it Submits data to the table and ShowErrorcell.value = 0

In a nutshell, as far as I can see, all the data validation is done on the sheet I have described above, which leads me to question where I would paste the VBA you sent me.

some Validation I will need, that I have not yet forced is to ensure that I enter data in Cell F8 before I enter data in F9. I thought I would figure out how to solve the problem of only having a drop-down list in F9 if Cell F8 = "Crew".


There are 4 VBA modules that link together to make the program work. which are copied and pasted below.

1st Module..................
Sub validate_form()
errorcount = Range("L4").Value
Set showErrorCell = Range("M4")

'unprotect the worksheet
ActiveSheet.Unprotect "123456"


'check for errors.
If errorcount > 0 Then
'Error!

' tell the user how many errors there are.
MsgBox errorcount & " Error(s)"

'Allow conditional formatting for errors to be displayed.
showErrorCell.Value = 1

Else
'NO Error - All Good

'Store the Data
' - call the macro form that does that.
Call Store_update_Data

'Success - Do something!
MsgBox "Data Submitted to Table!"

'Turn conditional formatting for erros off.
showErrorCell.Value = 0

End If

'Protect the Worksheet
ActiveSheet.Protect "123456"

End Sub

2nd Module.................

Sub Store_Data()
'Take Data from one worksheet and store it in the next empty row on another worksheet

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Integer

Set sourceSheet = Sheets("Form")
Set dataSheet = Sheets("Database")

nextRow = dataSheet.Range("C" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row

dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("F4").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F5").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("F6").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F7").Value
dataSheet.Cells(nextRow, 15).Value = sourceSheet.Range("F8").Value
dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("F9").Value
dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("F10").Value
dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("F11").Value
dataSheet.Cells(nextRow, 10).Value = sourceSheet.Range("F12").Value
dataSheet.Cells(nextRow, 11).Value = sourceSheet.Range("F13").Value
dataSheet.Cells(nextRow, 12).Value = sourceSheet.Range("F14").Value
dataSheet.Cells(nextRow, 13).Value = sourceSheet.Range("F15").Value
dataSheet.Cells(nextRow, 14).Value = sourceSheet.Range("F16").Value
dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("F17").Value
dataSheet.Cells(nextRow, 16).Value = sourceSheet.Range("F18").Value
dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("F20").Value
dataSheet.Cells(nextRow, 17).Value = sourceSheet.Range("F22").Value
dataSheet.Cells(nextRow, 18).Value = sourceSheet.Range("F24").Value

'Clear Data
sourceSheet.Range("F4").Value = ""
sourceSheet.Range("F5").Value = ""
sourceSheet.Range("F6").Value = ""
sourceSheet.Range("F7").Value = ""
sourceSheet.Range("F8").Value = ""
sourceSheet.Range("F9").Value = ""
sourceSheet.Range("F10").Value = ""
sourceSheet.Range("F11").Value = ""
sourceSheet.Range("F12").Value = ""
sourceSheet.Range("F13").Value = ""
sourceSheet.Range("F14").Value = ""
sourceSheet.Range("F15").Value = ""
sourceSheet.Range("F16").Value = ""
sourceSheet.Range("F17").Value = ""
sourceSheet.Range("F18").Value = ""
sourceSheet.Range("F20").Value = ""
sourceSheet.Range("F22").Value = ""
sourceSheet.Range("F24").Value = ""


End Sub

3rd Module........
Sub Select_Data()
'Search the data repository worksheet and return the found record into the form

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim SearchValue As Variant
Dim DataIdCol As Range
Dim recordRow As Integer

'Make some sheet variables so that we canuse those instead of hard-coding
Set sourceSheet = Sheets("Form")
Set dataSheet = Sheets("Database")

'Column that contains the value for which we will search.
Set DataIdCol = dataSheet.Range("B2:B2000")

'Value to search for.
SearchValue = InputBox("Input Vantage number)", "Search by Vantage Number")

'Check if the user inputs a value and hit the ok button
If SearchValue <> vbNullString Then
'Value input, so search for it.

'Clear Data
sourceSheet.Range("F4").Value = ""
sourceSheet.Range("F5").Value = ""
sourceSheet.Range("F6").Value = ""
sourceSheet.Range("F7").Value = ""
sourceSheet.Range("F8").Value = ""
sourceSheet.Range("F9").Value = ""
sourceSheet.Range("F10").Value = ""
sourceSheet.Range("F11").Value = ""
sourceSheet.Range("F12").Value = ""
sourceSheet.Range("F13").Value = ""
sourceSheet.Range("F14").Value = ""
sourceSheet.Range("F15").Value = ""
sourceSheet.Range("F16").Value = ""
sourceSheet.Range("F17").Value = ""
sourceSheet.Range("F18").Value = ""
sourceSheet.Range("F20").Value = ""
sourceSheet.Range("F22").Value = ""
sourceSheet.Range("F24").Value = ""


'Search
'More specific information: Range.Find method (Excel)
Set Rng = DataIdCol.Find(What:=SearchValue, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)

'check if a record was found.
If Not Rng Is Nothing Then
' Record Found!

'Get the row of the record.
recordRow = Rng.Row

'Put the records information back into the form.
sourceSheet.Range("F4").Value = dataSheet.Cells(recordRow, 2).Value
sourceSheet.Range("F5").Value = dataSheet.Cells(recordRow, 3).Value
sourceSheet.Range("F6").Value = dataSheet.Cells(recordRow, 4).Value
sourceSheet.Range("F7").Value = dataSheet.Cells(recordRow, 5).Value
sourceSheet.Range("F8").Value = dataSheet.Cells(recordRow, 15).Value
sourceSheet.Range("F9").Value = dataSheet.Cells(recordRow, 7).Value
sourceSheet.Range("F10").Value = dataSheet.Cells(recordRow, 8).Value
sourceSheet.Range("F11").Value = dataSheet.Cells(recordRow, 9).Value
sourceSheet.Range("F12").Value = dataSheet.Cells(recordRow, 10).Value
sourceSheet.Range("F13").Value = dataSheet.Cells(recordRow, 11).Value
sourceSheet.Range("F14").Value = dataSheet.Cells(recordRow, 12).Value
sourceSheet.Range("F15").Value = dataSheet.Cells(recordRow, 13).Value
sourceSheet.Range("F16").Value = dataSheet.Cells(recordRow, 14).Value
sourceSheet.Range("F17").Value = dataSheet.Cells(recordRow, 6).Value
sourceSheet.Range("F18").Value = dataSheet.Cells(recordRow, 16).Value
sourceSheet.Range("F20").Value = dataSheet.Cells(recordRow, 1).Value
sourceSheet.Range("F22").Value = dataSheet.Cells(recordRow, 17).Value
sourceSheet.Range("F24").Value = dataSheet.Cells(recordRow, 18).Value

Else
'Nothing found, tell the user
MsgBox "Record not found."

End If

End If


End Sub

4th Module
Sub Store_update_Data()
'TeachExcel.com
'Takes data from one worksheet and stores it in the next empty row on another worksheet
'or, if the record exists already, it will be updated.

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Integer

Set sourceSheet = Sheets("Form")
Set dataSheet = Sheets("Database")

'Column that contains the value for which we will search.
Set DataIdCol = dataSheet.Range("B2:B2000")

'Value to search for.
SearchValue = sourceSheet.Range("F4").Value

'Check if the user inputs a value and hit the ok button
If SearchValue <> vbNullString Then
'Value input, so search for it.

'Search
'More specific information: Range.Find method (Excel)
Set Rng = DataIdCol.Find(What:=SearchValue, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)

'check if a record was found.
If Not Rng Is Nothing Then
' Record Found!

'Get the row of the record.
nextRow = Rng.Row

Else
'Input new record!
'Get the next empty row from the Data sheet.
nextRow = dataSheet.Range("C" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row

End If

dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("F4").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F5").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("F6").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F7").Value
dataSheet.Cells(nextRow, 15).Value = sourceSheet.Range("F8").Value
dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("F9").Value
dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("F10").Value
dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("F11").Value
dataSheet.Cells(nextRow, 10).Value = sourceSheet.Range("F12").Value
dataSheet.Cells(nextRow, 11).Value = sourceSheet.Range("F13").Value
dataSheet.Cells(nextRow, 12).Value = sourceSheet.Range("F14").Value
dataSheet.Cells(nextRow, 13).Value = sourceSheet.Range("F15").Value
dataSheet.Cells(nextRow, 14).Value = sourceSheet.Range("F16").Value
dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("F17").Value
dataSheet.Cells(nextRow, 16).Value = sourceSheet.Range("F18").Value
dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("F20").Value
dataSheet.Cells(nextRow, 17).Value = sourceSheet.Range("F22").Value
dataSheet.Cells(nextRow, 18).Value = sourceSheet.Range("F24").Value

'Clear Data
sourceSheet.Range("F4").Value = ""
sourceSheet.Range("F5").Value = ""
sourceSheet.Range("F6").Value = ""
sourceSheet.Range("F7").Value = ""
sourceSheet.Range("F8").Value = ""
sourceSheet.Range("F9").Value = ""
sourceSheet.Range("F10").Value = ""
sourceSheet.Range("F11").Value = ""
sourceSheet.Range("F12").Value = ""
sourceSheet.Range("F13").Value = ""
sourceSheet.Range("F14").Value = ""
sourceSheet.Range("F15").Value = ""
sourceSheet.Range("F16").Value = ""
sourceSheet.Range("F17").Value = ""
sourceSheet.Range("F18").Value = ""
sourceSheet.Range("F20").Value = ""
sourceSheet.Range("F22").Value = ""
sourceSheet.Range("F24").Value = ""

End If

End Sub

My apologies if I have missed anything, I've not had as much time as I would have liked to go through everything, but I have tried to be thorough with the information I am providing.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
Your explanation is quite involved and would be difficult to understand without seeing your actual file. The code I suggested uses a Worksheet_Change event which is placed in the worksheet code module. It works independently from the code in the regular modules. However, the code you posted could affect how the one I suggested works. It is not a matter of just inserting it somewhere in your existing code. I'm afraid that unless I have access to your actual file (de-sensitized if necessary), it would be very difficult for me to help and even then, I can't promise you a working solution. Could upload a copy of your file to a free site such as www.box.com or www.dropbox.com ? Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.
 

CaptainChaos

New Member
Joined
Jun 11, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Mumps.
Have santitized. Some attached worksheets removed, and tried to concentrate on the area where the problem is.
The passwords to unlock sheets are on the worksheet "List" and have put some dummy data in.

Dropbox link below as requested.


 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624

ADVERTISEMENT

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Form" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the password (in red) to suit your needs. Close the code window to return to your sheet. Make a selection in cell F8. This version of the macro also forces a selection to be made in F8 before data is entered in F9.
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("F8:F9")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="123456"
    Select Case Target.Row
        Case Is = 8
            If Target = "RIG CREW" Then
                With Range("F9").Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:="=OFFSET('List'!$A$2,0,0,COUNTA('List'!$A:$A),1)"
                    Range("F9").Select
                End With
            ElseIf Target = "" Then
                Range("F9").Validation.Delete
                Range("F9").ClearContents
            Else
                Range("F9").Validation.Delete
                Range("F9").Select
            End If
        Case Is = 9
            If Range("F8") = "" Then
                MsgBox ("Please select a CATEGORY first.")
                Range("F9").ClearContents
                Range("F8").Select
            End If
    End Select
    ActiveSheet.Protect Password:="123456"
    Application.EnableEvents = True
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
I had to make some changes and updates to your other macros because some of them were affecting the macro I suggested. Click here to download your file and give it a try.
 

CaptainChaos

New Member
Joined
Jun 11, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Form" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the password (in red) to suit your needs. Close the code window to return to your sheet. Make a selection in cell F8. This version of the macro also forces a selection to be made in F8 before data is entered in F9.
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("F8:F9")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="123456"
    Select Case Target.Row
        Case Is = 8
            If Target = "RIG CREW" Then
                With Range("F9").Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:="=OFFSET('List'!$A$2,0,0,COUNTA('List'!$A:$A),1)"
                    Range("F9").Select
                End With
            ElseIf Target = "" Then
                Range("F9").Validation.Delete
                Range("F9").ClearContents
            Else
                Range("F9").Validation.Delete
                Range("F9").Select
            End If
        Case Is = 9
            If Range("F8") = "" Then
                MsgBox ("Please select a CATEGORY first.")
                Range("F9").ClearContents
                Range("F8").Select
            End If
    End Select
    ActiveSheet.Protect Password:="123456"
    Application.EnableEvents = True
End Sub
 

CaptainChaos

New Member
Joined
Jun 11, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Mumps.
To say I'm impressed would be an understatement.

I had been going around in ever decreasing circles looking for answers on youtube, and I was never going to find the answer as it requires VBA which is something I'm only just beginning to get to grips with. You have saved my sanity.
Your fix works like a charm.
Thank you for your help.
 

Forum statistics

Threads
1,141,773
Messages
5,708,453
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