Data Validation

[macmet]

Hello Everyone,

I am fairly new to using drop down menus in Excel, but I have managed to get a few working and have them linked so the drop down menu is dependent on an initial selection. For example, you can see the webpage here I am using to make the conditional lists.... ->

http://blogs.office.com/2009/11/24/create-conditional-drop-down-lists/

My only issue is that if I make an initial section in A1, which gives me a drop down menu in B1 based on the selection in A1. After I make the selection in B1, if I go back and change A1, it is possible that I am left with an "impossible" combination. I believe this is what was being discussed in the older thread below.

thread770-165533

I'm wondering if it is possible in the newer versions in Excel to automatically change or clear the selection in B1 if A1 is changed after.

I hope that makes sense. I can provide screenshots if I need to provide more clarification.


Cheers,
Macmet

 

[SkipVought]

Hi,

Resetting the dependent DropDown is something you must do with your sheet, via Event code that behaves: when A1 changes, reset all dependent DropDowns. This can be accomplished in VBA in that sheet's VBA Code module in the WorkSheet_Change event.

How is your Data Validation LIST referenced in your dependent DropDown?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[macmet]

Skip,

I appreciate the feedback. I am on the road with the spreadsheet I was using. When I get back to the office I will post more details.

Thank you


Macmet

 

[SkipVought]

Standing by.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[macmet]

Wow, I cannot believe this been a month. But I am back using the sheet again.

I am using the Indirect function in my dependent pull down menu.

For example I have my first pulldown menu in C9 with the reference to my TASKS list.

The second pull down menu is in E9 I have,

=INDIRECT($C$9)

Then then I also have another pulldown in F9 where it is,

=INDIRECT($E$9)

This works when I go through and make the selections in the proper order. If I want to go back and change the selection picked in the list in C9, the selections made in E9, F9 stay the same. What would be great would be any time I changed the selection in C9, the cells in E9, F9 would go blank. And also if I were to change the selection in E9 only, the F9 cell would go blank.

I hope that makes sense.


Macmet

 

[SkipVought]

You're going to have to use VBA to do this. This is done with Change Event in the Excel sheet.

Here's the way to find these Events:
[tt]
1) Right-click the Sheet TAB and select View Code. This exposes the Code Sheet in the VBA Editor for this Sheet. (Alt+F11 will toggle between the Sheet and the VBA Editor.)
2) Just above and on the left-hand side, is the Object Drop Down. You'll see (General) initially. Click the Drop Down and select Worksheet.
3) Just above and on the right-hand side, is the Procedure Drop Down. You'll see Selection Change initially. Click the Drop Down and select Change, because we want to do something when some value changes in C9, your initial Drop Down.

This is what you'll see in the Code Sheet...

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

So here's what we need to happen: When you make a new selection, ie change the value in C9, the contents of E9 & F9 need to be cleared.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Cells(9, "C")) is nothing Then
      Cells(9, "E").ClearContents
      Cells(9, "F").ClearContents
   End If 
End Sub

So although this event fires any time ANYTHING changes value in this sheet, the ClesrContents only occurs when The Target coincides with C9.
[/tt]

Give the E9 change a try yourself and see if you can make that work. Post back if you need more help.

Hope that works for you.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[macmet]

Skip,

That works exactly how I wanted it.

Does that code become part of this spreadsheet? I.e. if I send it to my colleagues, will the ClearContents work for them or do they need to do that VBA code?

Thanks,
Macmet

 

[SkipVought]

It is in the sheet. However, they will need to Enable Macros to get this functionality.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}