How to clear the data validation cell

faulkneralder

New Member
Joined
Jul 5, 2007
Messages
9
Hello,

I have set up a data validation List cell with a series of list values that changes depending on the value typed in another cell. The user can then select from that generated list and the value is shown in the data validation cell. The setup is like this:

Cell B4 = cell that the user types some freeformat text in
Cell B6 = data validation cell refering to list
Cells B7 to B100 is the list

The list in B7:B100 changes depending on what the user types in B4. So the user could type "soft" in B4 and the list would show all values that have the text "soft" in them. The user then goes to B6 to select the actual entry from the list they want.

What I want to do is after this is done, if the user then deletes B4 or starts to type something new, not only does the B7:B100 list change (which it does ok) but also the value that was previously selected in B6 is erased, or at least is reset automatically to the top value of the B7:B100 list (which is "(Select the actual value from this list)"). Unfortunately although the list itself changes when B4 is re-types, B6 continues to show the old selected value until a new selection is made.

Is there a simple way I can reset B6 automatically when B4 is changed?

Thanks, Peter
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Right click on the sheet tab, select view code and pop this code on the page that appears:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B4")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Range("B6").ClearContents
End Sub

Will clear B6 each time B4 is updated.

Dom
 

RONS1331

New Member
Joined
Nov 16, 2015
Messages
1
Is there a way to do this for various Data Validations in a worksheet tab?

For example I have 10 rows of standard data validation lists where you can select different generalized type of products.

Basically I have an equipment chassis with ten modules that you can install.

The first row of 10 data validation you select the type of module.

The second 10 rows underneath (=indirect) you can then select the different models available for the above selected type.

The problem is that if you change the type the below model stays the same.

I want the model to revert to a blank is the type selected is no longer valid model for that type.

This formula works great for just one data validtion, how would you do various in the same worksheet?

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

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