Forum Discussion
Data validation dropdown list isn't working
Just recently, the data validation I have set up for my worksheets no longer shows a dropdown list or arrow in the cell for my users. You can start typing something in, and it will give options close to what you've typed in if it's on the list but it no longer gives a dropdown list to choose from. I do have the appropriate boxes checked to allow for that and it's still not working. It's incredibly frustrating to find more and more things no longer work properly since the most recent update.
Has anyone else had an issue with this?
srugh I can't say that I have. One possible cause is that objects have been hidden for the entire workbook. Go to File > Options > Advanced, then scroll down to "Display options for this workbook:" and make sure "All" is selected under "For objects, show:".
Advanced Options
It's also a good idea to double-check the data validation rules to make sure "List" has been selected and the "In-cell dropdown" checkbox has been checked.
Data Validation List
If indeed the settings are correct, try clearing the data validation rules and recreating them from scratch.
Lastly, if Freeze Panes has been applied to the neighboring column (on the right) and the horizontal scroll position is too far to the right, the drop-down arrow may appear to be missing when all you need to do is scroll back over to the left.
Freeze Panes: Column D hidden by horizontal scroll position
If none of these suggestions apply to your situation, it's possible the workbook has become corrupted. You could try restoring the file from a backup (if available) to see if the problem exists in a prior version of the file; or, try recreating the data validation rules in a new worksheet to see if the problem persists.
Kind regards.
7 Replies
Hi Dear Surge
I am also facing trouble to get Data validation drop down list, even though all setting done as djclements mentioned here, but till now i can get dropdown list, could you please assist me to fix it?
Huge thanks in advance.Hi guys
Chris here from South Africa.I have a similar issue:
(Just saying - everything was working perfectly for 7 years)
We have all our Excel files with data validation in Sharepoint.
I use a lot of data validation for users to select products form a list, but it is setup as Warning Only so if a product is not in the list the user can still enter his own product - it will give warning and accept.
Now suddenly if I go into ANY of the Excel files (not just one specific) it gives the warning, but as soon as I say YES, it just moves to next cell leaving previous one empty!
My collogue logs into the same file and it works.Even stranger, if she on her own logs in it works, if I should from my laptop log into the same file at the same time, it stops working for her!!!
Any ideas??I am the sight owner so have full permissions and as I have said, worked perfectly before.
I faced the same problem with the in-cell dropdown list not appearing.
It was caused by deleting shapes one by one, as shown in the code below.
Dim obj as Object For Each obj In ActiveSheet.Shapes obj.Delete Next objAccording to the https://learn.microsoft.com/en-us/office/vba/api/excel.shapes?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaxl10.chm637072)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue, bulk operations involving a set of shapes (e.g. all shapes of certain type, or all shapes in the worksheet) should be performed using a ShapeRange property as shown below.
ActiveSheet.Shapes.SelectAll Selection.ShapeRange.Deletesrugh I can't say that I have. One possible cause is that objects have been hidden for the entire workbook. Go to File > Options > Advanced, then scroll down to "Display options for this workbook:" and make sure "All" is selected under "For objects, show:".
Advanced Options
It's also a good idea to double-check the data validation rules to make sure "List" has been selected and the "In-cell dropdown" checkbox has been checked.
Data Validation List
If indeed the settings are correct, try clearing the data validation rules and recreating them from scratch.
Lastly, if Freeze Panes has been applied to the neighboring column (on the right) and the horizontal scroll position is too far to the right, the drop-down arrow may appear to be missing when all you need to do is scroll back over to the left.
Freeze Panes: Column D hidden by horizontal scroll position
If none of these suggestions apply to your situation, it's possible the workbook has become corrupted. You could try restoring the file from a backup (if available) to see if the problem exists in a prior version of the file; or, try recreating the data validation rules in a new worksheet to see if the problem persists.
Kind regards.
In your reply you do not include ListItems! as part of the Source code. This is not how the process is described in web page https://support.microsoft.com/en-au/office/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b
- That was the issue! Object display was set to 'Nothing (hide objects)'. No one but me should be messing with the sheet which is what caused the confusion but clearly something happened to change that setting. Thanks so much!
srugh Glad to hear it! You're very welcome 🙂
