Excel Dropdown Blank [User Can't Click Cells with Dropdown]

This really depends on whats causing the issue.

Are the drop downs data validation or are they controls?

Are the drops downs filled via a formula or a macro?

Are the people opening the file with Macro's enabled?

Are they getting (downloading) the file from a network share, site like SharePoint or dropbox, or a website? If so, right click on the downloaded file, goto properties and look at the general tab. If there is an unlock button click it. This may resolve the problem.

Does it work properly for some people and not others? If so do they have different versions of Office/Windows?

Has anyone tried opening in safe mode? Do the people with the problems have any addins activated (COM or Excel)? If so try disabling them and see if it works. Its not uncommon for addins to interfere with normal operations.

If these people create a new blank file with a similar drop down does it work properly? Test a simple file with the same basic functionality to determine if its the file or a software/OS issue.

Any information or samples you can provide would certainly help us help you.

Thanks! Here are answers:

The dropdowns were created via data validation (List, with in-cell dropdowns, set up to ignore blanks).
They do not populate via macro- they simply have the source of a specific range on another sheet in the workbook for the names. The dropdown itself is working just fine and populating properly- the problem is that people can't click into the cell with the dropdown without using the arrows on their keyboard to navigate to the correct cell.

People are opening the file with macros enabled- again, the macros are working, all the content is pulling- people just can't use their cursor to click on the necessary cells.

We're downloading files directly via email.

No one can directly click on the cells, even with different versions of office.

We have several different versions of the same spreadsheet and the others work, just not this one.

I can't replicate the problem elsewhere, and the spreadsheet that I'm using has some proprietary info that I can't share.

Jamie, welcome

Please dont use "Reply with Quote", its just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

Also, To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Are you sure that this IS a dropdown (Data validation, and not a combo box or active-x control?
If you click the cell, then click Data/Data Validation, what happens?

(any chance of just copying that sheet to another file, then sharing that?)

Further to what Ford has said, you can delete non-essential data and change names to Mickey Mouse, etc.

If it comes from email, its possible the file is locked, did you try my directions to see if there was an unblock button? Right click the file | Properties | General tab. See the screenshot below

unblock.jpg

Have you ensured no addins are activated? Since its a data validation drop down and not backed by macros it should function in safe mode. If it does an addin is the likely cause. You would then in normal startup disable all addins and enable them one at a time until the issue resurfaces, pin pointing the specific add-in to blame

From the sounds of it, it does seem that its an issue specific to this file as you have said you cannot replicate the issue (that doesnt mean it cant be a combination of something in the file not agreeing with some external factor like an addin). Make a copy of the file (to test) and try to simplify it as much as possible. Remove protections, halt macros that may run automatically (event driven) and see if any change gets it to work. Specifically, I would try unmerging the cell too, as from the image you posted it appears to be in a merged cell. I avoid merged cells like the plague...actually I'd rather have the plague than use a merged cell in my own files, they are nothing but problems for a visual effect that can be accomplished numerous other ways.

You could try (on a copy!) open & repair as well, in case the issue is a matter of corruption in the file.

Lets see where that gets us

For me its the protection causing an issue it seems on the face of it.

I opened the file and can click the drop down but its empty. Unprotect the sheet and the drop down works. Ill take a deeper look at it and see what else we can find

EDIT: Maybe a little premature, see this gif:

xl.gif

I can click the cell of the drop down and only get the list when that cell is selected, otherwise the cell is empty

Ah the real issue...

I think there is some corruption in the workbook, likely caused by how the drop downs are being created. As you can see 7 exist. I think one is preventing selecting the cell when protection is turned on (note how the mouse pointer changes when you mouse over the drop down/cell with protection on). That generally happens with objects on sheet.

What leads me to corruption being at play here, is you cannot select nor delete these 7 drop downs listed on the sheet. Further, #7 keeps hiding itself and if you click show, the drop down arrow displays in whatever cell you currently have selected. Also I am unable to delete any of these 7 by using clear data validation.

Are these drops downs created by the macro?

You said you have past versions of the file, does the selection pane show these extra objects?

asdfasdf.PNG

Ill see if I can dig up any more.

Also, what method did you use to get the drop downs arrow to display even when the cell isnt selected? That could be a cause of the issues.

How do I enable viewing of the selection pane?

Also, I don't think the dropdowns are created by a macro- it looks like a simple list-type source validation based on a range.

Edit:
I didn't create this workbook from scratch- I've modified it a lot, but this page is one that I haven't messed with. I don't know why the dropdown arrow shows. In versions where the dropdown works properly, though, you're right- the dropdown arrow goes away.

Its Home | Find & Select | Selection Pane.

The file has some strange behavior. I think you are right about the VBA, from what I saw none of it creates the drop downs. I do however think whoever made them used some trickery to show the drop down arrow all the time or its just a symptom of corruption. I couldnt find any obvious signs of the typical methods to keep it showing all the time.

Id say try an open and repair on a copy of the file if you have not already, see if it can fix the file.

EDIT: look what happens when you zoom in on the sheet, this is with no change other than unlocking the sheet. You can distinctly see 2x drop down arrows and the one that functions is obviously different than the other.

asdfasdf.PNG

Glad to hear you have a work around. I however would like to point out that you have simply hidden the symptoms, not cured the disease. I wasnt able to find a clear fix myself. The underlying xml that comprise the file show only a single drop down as expected, which is a very odd thing and sign of corruption. Continuing to use the file as is could lead to the file becoming more corrupt, less stable or even unable to be opened in the future. If you are unable to fix the file directly I would highly recommend moving to a new file/rebuilding a new file. It could end up saving you alot of time and headaches down the road.