Dependent Drop-Down List

palaeontology

Active Member
Joined
May 12, 2017
Messages
444
Office Version
  1. 2016
Platform
  1. Windows
I have two drop-down lists, the first chooses which year level the user wishes to choose from, the second populates with students who belong to the year level chosen in the first drop-down list .. see below ...

excel query.JPG


If I make a choice of student, (for example .. Laila Brighton), it will, of course, display that student name, however, if I return to choose a different student name, the drop-down list will only show the names in the list from Laila Brighton down .. in other words, any name higher than that name will not be shown unless I deliberately scroll back up to see them.

excel query 2.JPG


This becomes a problem, of course, when I choose a name right down the bottom of the list, so if I return to choose another name, only the very bottom name(s) would be displayed.

Now if I'm the only user, that's not an issue as I know to scroll back up, however, the spreadsheet is accessed by a large number of teachers, most of whom have little excel knowledge, so inherently if they choose a name down the bottom of the list, when they return to choose another name from that same year level, they think the drop-down list has no names to offer ... they don't know to scroll back up to find the other names.

So ... is there a way to force the drop-down list to ALWAYS return to the top of the list each time it's accessed ?

Important : I would like to avoid using combo boxes as they tend to have other issues, such as expanding and shrinking depending on the resolution of the user's monitor etc.

I'd like to stick with drop-down lists with data validation if at all possible.

Can this be done ?

Very kind regards,

Chris
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
That a feature: Excel locate the "nearest" item in the list that match the existing value
If it's a problem you could clear the cell so that the dropdown list will show from the beginning (PROVIDED THAT the validation list don't have an empty line in the middle or, worse, at the end)
To clear the cell you can use a "SelectionChange", with a macro like this one.
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myCells As String
'
If Target.Count > 1 Then Exit Sub
myCells = "M2:M100"                     '<<< Range of cells to be cleared when selected
'
If Not Application.Intersect(Target, Range(myCells)) Is Nothing Then
    Target.ClearContents
End If
End Sub
From Excel, rightclick on the tab with the name of the sheet you work on, select "Show code"; This will open the editor of the macros at the right module; copy the code and paste it into the empty module. In case that othe macro are already in the module, then we need to check if there are compatibility problems.

The line marked <<< need to be customized with your information; in the above example I assumed that the data validation is in range M2:M100

BEWARE that this method has "THE" inconvenient that the cell will be cleared even if the user went there only to better focus on the content of the cell...

Bye
 
Upvote 0
Hi Anthony, I must be doing something very wrong .. I've entered your code but the problem persists.

The cell bearing the drop-down is in .... 'Character Lookup List'!J6

The source list for that data validation is in .... 'Data Validation'!P4:P16 ... which looks like this ...

excel query 3.JPG


Note, there are no empty cells in the source list.

I adjusted your code to the following ...

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myCells As String
'
If Target.Count > 1 Then Exit Sub
myCells = "J6"                     '<<< Range of cells to be cleared when selected
'
If Not Application.Intersect(Target, Range(myCells)) Is Nothing Then
    Target.ClearContents
End If
End Sub

but when I click on one of the names low in the list, the next time I return to the drop-down, to select a different name, it's still not returning the list to the top name.

Is there something I've done incorrectly ?
 
Upvote 0
So you have only cell J6 in sheet "Character Lookup List" with that validation...

Is J6 cleared when you reselect it?
if Not, then we should review where the code has been inserted:
-rightclick on the tab with the name "Character Lookup List", select Show Code (or a similar available choice, my Excel is not in English); is the code inserted into the vba module that opens after such a choice? I Not, then insert it here and retest. In this case also rightclick on the tab with the name "Data Validation", and if the code has been inserted there just delete it

Bye
 
Upvote 0
I already had it inserted in the correct area for the correct sheet, but still nothing.

There are no other codes in this spreadsheet to potentially confuse things.

Is there anything else in the code that needed changing seeing as it's only the one cell with the data validation ?
 
Upvote 0
BUT when you reselect J6, the contents get cleared or not?
Has the workbook been saved as ".xlsm", i.e. Macro enabled? and when you open the workbook, are the macro enabled?

Add this instruction in this position:
VBA Code:
If Target.Count > 1 Then Exit Sub
Beep                               '+++ ADD THIS LINE in this position
myCells = "J6"                     '<<< Range of cells to be cleared when selected
Now do you hear a beep every time you select J6?

Bye
 
Upvote 0
definitely a macro-enbaled file, and definitely enabled.

I'm hearing a beep every time i click into any cell, not just J6
 
Upvote 0
But WHY you dont respond to my repeated question:
"DOES J6 GET CLEARED WHEN YOU RESELECT IT?"

To answer the question:
-select J6 and set a value into the cell from the drop down list
-select J7 or another celle of your choice
-select J6
-Look to the cell and check if the cell is cleared or not

THANK YOU
 
Upvote 0
Anthony47 said:
DOES J6 GET CLEARED WHEN YOU RESELECT IT?
You didn't yet answer the question, but my best guess is that you missed the fact that the proposed solution require that you (re)select J6.
However if you select J6 and then select a value from the data validation dropdown list, then selection REMAINS on J6. In this case if you open again the dropdown list (without ever leaving J6), the list will show from the set value, and not from the beginning.
If you have moved your selection out of J6, instead, then if you return to J6 (to make a new choice) the macro should clear J6 and the dropdown list would open from the first line


Anthony47 said:
BEWARE that this method has "THE" inconvenient that the cell will be cleared even if the user went there only to better focus on the content of the cell...
If you still agree to test the proposal, we can force J6 to be deselected after any entry, be it via keyboard or via dropdown list; to obtain that, you should insert the following Macro into the same vba module that currently hosts Sub Worksheet_SelectionChange:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$6" Then
    If Len(Range("J6").Value) > 0 Then Range("J7").Select
End If
End Sub
Looking forward to hearing from you...
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top