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

[dellzy]

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.

 

[Arafat]

Hi,

Use combo boxes instead, you will have more control over it.

 

[L. Howard]

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

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]

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!