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

[Jean_Sibelius]

Hi everyone,

Hope that someone can help me with the following struggle I have.

I created a worksheet having dropdown selections that depend on the content of another cell.

In the attached example the content of
D1 (which is a dropdown) determines which selection can be made in E1 (also a dropdown)
The same for
F1=> G1 and
H1 => I1

I have a few questions.

There is also an option ' - ' in the dropdown selections of D1, F1 and H1.
What can I do to automatically change the content of the corresponding cell to ' - ' also?
e.g. if D1 is changed into ' - ' I would like that E1 automatically changes to ' - '.
See e.g. Cell I1 which contains "A method" although H1 = ' - '.

In addition, is it possible to somehow indicate that if the content of D1 is changed the actual value in E1 should also be changed (ideally by activating the right drop down selection), but a color warning would also be nice.
So if I change the content from D1 being a "Method general" while E1 is showing/selected "A calibration method", how to flag that E1 should change when the content of D1 changed?

Last question .
In Cell A1 I included a "check box"
Is it possible to show/activate the content of cells D1:I1 when the check box is checked and to make cells D1:I1 empty when this checkbox is unchecked?
If possible, I want to use multiple check boxes for different types of content, but having a solution for these issues would already be great.

Hope the questions are clear. Any help and suggestions are highly appreciated!


Best regards, Jan

[BadlySpelledBuoy]

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

[Jean_Sibelius]

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

[BadlySpelledBuoy]

Perhaps like this?

Please Login or Register  to view this content.

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

[Jean_Sibelius]

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

[Jean_Sibelius]

<I found the answer>... the checkbox was referring to the wrong Macro :-/... (lesson learned)

== Obsolete.

In addition, what also seems to be weird, but likely give a direction of the issue (and solution).

When I copy the worksheet from the example as edited and provided by you as a one-on-one copy in the other worksheet...

But keep the name of the checkbox ("CheckBox9") and the worksheet ("Template") identical.

If I then press on "CheckBox9" it (re)opens the workbook as provided by you (even if it was closed)?!?

Any clue where the issue can be found? Thanks again.


Best regards, Jan