Automatically changing the content of a 2nd (dependent) dropdown selection

Q1) The below piece of code in the worksheet module will change the corresponding cell to "-"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D1,F1,H1")) Is Nothing Then
        If Target = "-" Then Target.Offset(, 1) = "-"
    End If
End Sub

Q2) You can use a COUNTIF formula in conditional formatting to change the corresponding cell colour if the value currently selected does not match a possible option. The formula below is for the conditional formatting of E1.

Formula:

=COUNTIF(Method!$J$4#,$E$1)=0

Q3) It't not clear to me what the ask is here. Do you want to clear the contents of D1:I1 when the checkbox is unchecked and put the same values back in when it's checked? If so you'll need to store the information somewhere else in the workbook to be able to retrieve it, but it's possible.

BSB

Dear BSB,

Thanks a lot for your suggestions and solutions!

My first and second questions are completely answered and solved.


Wrt the clearing of the content. It would already be very nice if dependent on the checking or not checking the form adapts.


Attached how these two scenarios could look like (if based on your experience you have another suggestion, please feel free to provide your input).

What I was thinking:
Not checking, cleaning the content of the cells (not necessary to store), white text and white fill.
When checking again. Use a slightly different coloring for the first level dropdowns and the second level dropdowns, black text and every cell filled-in with a '-'.

Thanks again for your help and hope that you can also help me with this question.

Best regards, Jan

Perhaps like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Not Intersect(Target, Range("D1,F1,H1")) Is Nothing Then
            If Target = "-" Then Target.Offset(, 1) = "-"
        End If
    End If
End Sub


Sub ShowHideValues()
    Select Case Me.Shapes("Check Box 9").ControlFormat.Value
        Case 1
            With ThisWorkbook.Sheets("Template")
                .Range("D1,F1,H1").Interior.Color = 15395562
                .Range("E1,G1,I1").Interior.Color = 12632256
                .Range("D1:I1").Font.Color = vbBlack
            End With
        Case Else
            With ThisWorkbook.Sheets("Template").Range("D1:I1")
                .Value = "-"
                .Interior.Color = vbWhite
                .Font.Color = vbWhite
            End With
    End Select
End Sub

See attached.

One issue I can see is the user will still be able to use the dropdown lists even when the checkbox is "empty" and that could have data integrity implications.
It can be mitigated by removing the data validation when checkbox is cleared and reinstating it when the checkbox is checked.

BSB

Dear BSB,

Thank you for your suggestion, which works indeed in the attached *.xls as it should be.

I copied the code in my parent Excel and only change the name to the correct checkbox ("CheckBox1") and I changed the cells into the corresponding cells in the parent Excel (as you see row 7 instead of row 1).

Sub ShowHideValues()
Select Case Me.Shapes("CheckBox1").ControlFormat.Value
Case 1
With ThisWorkbook.Sheets("Template")
.Range("D7,F7,H7").Interior.Color = 15395562
.Range("E7,G7,I7").Interior.Color = 12632256
.Range("D7:I7").Font.Color = vbBlack
End With
Case Else
With ThisWorkbook.Sheets("Template").Range("D7:I7")
.Value = "-"
.Interior.Color = vbWhite
.Font.Color = vbWhite
End With
End Select
End Sub

If I change the setting of CheckBox1 and run the code in the developer, it works fine. If I check or uncheck CheckBox1 (expecting that the VBA-code would run) nothing happens however.

Already tried a few things... but so far not successful. Any suggestions what to check?

Furthermore, I agree that deactivate and active the dropdowns is a good improvement.

Is dropdownCell.Validation.InCellDropdown = True and False the way to go or do you suggest something else?

Again thanks for you support!

Best regards, Jan