2 Dependant list - 2 clear 2nd list input when selection change in 1st list

dellzy

Board Regular
Joined
Apr 24, 2013
Messages
146
Hi Experts,

I have 2 validation lists. 1 is options of Quarter number and 2 is the month names. 2nd list content will depend on the selection on 1st list. eg. if user select Q1 in 1st list, 2nd list will only show Jan, Feb and Mar.

However, if I make the next change in 1st list, say Q2, the last option I selected in 2nd list will still remain there. I want it to be totally clear or maybe auto show the first month of the selected quarter.

eg. I selected Q1, then select February. Then I selected Q2, but February still remain displayed. only when i click the validation list then i can see the month names of Q2 as per selected.

Any idea how to auto-clear the cell for list 2 after change selection in list 1, please?

tqvm.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Try this in the sheet module with the drop downs.

Where C2 is the Quarters drop down and C3 is the Months drop down.

Change those cells in the code to match your sheets drop down cell.

When properly matched, whenever you make a selection in the first drop down, the second one's last entry is cleared and that drop down cell is selected, ready for a choice to be selected in it.

Howard

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
  Range("C3").ClearContents
  Range("C3").Select
End Sub
 

dellzy

Board Regular
Joined
Apr 24, 2013
Messages
146
Hi,

Thank you for the prompt responses. Sorry, I can't use Arafat's solution on combo boxes as I understand combo boxes allow users to enter additional input or edit the current one. I want to restrict the users to just use the options defined in the named range.

As for L.Howard's solution, the macro line on clearcontents has benefited me on the solution in my original macro module. Thanks, L.Howard's!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,871
Messages
5,638,773
Members
417,052
Latest member
Noobest

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