dependent drop down lists with conditional formulas

[vze]

I have 3 columns
Column A= Status (values are Open and Closed)
Column B = List 1 (values=a series of RootCause values to choose from)
Column C = List 2 (values = a series of Sub Root Cause values which appear based on the Column B selected value)
Column C is a dependent drop down list to Column B. Both tables used in the drop down lists are tables and reference Defined Names.

I want Column B to be a required value ONLY if Column A = Closed. if Column A <> Closed, Column B is not required.
(Col B Values can be grayed out, left visible or another message can display. The key is Column B being required if Column A = Closed. This is to prevent a user from missing to fill in Column B.)

Similarly I want to make Column C required if Column B is not blank or null.

How do I add this to VB Code.
Because the drop down list is referencing the Defined Name as the "source" in the Data Validation tab, I cannot add a formula as the source and properly reference this Defined Name and have it successfully work. Therefore I thought to add VB code.

I am novice and need to know exactly what , where to add.

 

[Akuini]

@vze, welcome to the Forum.
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

 

[vze]

unfortu

@vze, welcome to the Forum.
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

unfortunately it will take me too long to try and recreate the same example in a sample workbook. The original workbook does contain sensitive information.

I am using the following existing VB code.
Ideally I would like to be able to write something that "on save" of the workbook, it checks to see if the column says "required" and throws a message box to force the user to populate this cell with a valid drop down list value. One of the challenges is if the user doesn't update the cell and leaves the verbiage "required", and you try to save or delete rows, the workbook throws an error, causes it to go into debug mode and everything stops working. This happens because its a dependent drop down box that is displaying values based on the prior column value. I am suggesting to write the word "required" once the 1st column value is selected so the user realizes they cant leave this cell blank. To avoid some of the errors, I added to clear the value of dependent column 2 if the parent column value is deleted to a blank. prior it was leaving the word "required" in the 2nd column after column 1 was blanked out. (hope this makes sense).

Bottom line is how can i get the workbook to validate if the column shows the word " required" to not allow saving nd force user to fix the workbook. This is the minimum solution I need right now for columns B and C.


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Column = 9 And Target.Validation.Type = 3 Then
Target.Offset(0, 1).Value = "required"
End If

If Target.Column = 9 And Target.Value = "" Then
Target.Offset(0, 1).Value = ""
End If


Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True

End Sub