Excel 2010 stops working when double clicking on a cell with a data validation list

Dear colleagues,

The issue I have specifically occurs when I double click on a cell with a data validation list.

Situation:
- I have a cell containing a data validation list  > works fine
- I should be able to select a product from the list or type in free text > works fine
- I should be able to edit the free text > doesnt work > the text disappears.

My findings are:
- Clicking (1x) on the cell with the data validation list shows the "busy" mouse cursor briefly.
- Double clicking on the cell with the data validation list also shows the "busy" mouse cursor running and receive the message Microsoft Excel has stopped working
- Im not able to edit the text in the cell (which is normally possible if you double click the cell)
- Excluding the macros removes the issue only partially (Excel doesnt stopped working anymore) but it is still not possible to edit the text in the cell because the text just disappears when double clicked on the cell. Pressing esc then shows the text again (=logic). When Enter is pressed, the text is then gone. 

Can anyone please help me with this?

October 12th, 2012 6:01pm

any idea's?
Free Windows Admin Tool Kit Click here and download it now
October 16th, 2012 9:35am

Hi,

Have you tried repairing Office programs?

Is there any third party add-ins installed to Excel?

Start Excel in safe mode:

Start > Run,

Excel.exe /safe

Press Enter and then open your workbook.

To allow entering in a data validation list, you should cancel the following option:

Click the Error Alert tab, uncheck the option Show error alert after invalid data is entered.

Best re

October 16th, 2012 9:58am

Thank you very much for your reply Rex. I appreciate this very much.

There are no third party add-ins installed to Excel.

I have tried what you suggested (safe mode) but this didn't fix the issue.

What I did noticed is:

With the sheet un-protected and design mode turned on, it works fine.

With the sheet protected and design mode turned on, I can double click on the cell without Excel crashing but still unable to change the cells with free text.

To avoid that it had something to do with the protection, I have tried all possible combinations of security but no luck, the issue remains the same... Excel has stopped working.

Is this any help Rex or does anybody has other idea's?

Thank you beforehand.

Free Windows Admin Tool Kit Click here and download it now
October 18th, 2012 4:43pm

I also cannot add values when the sheet is protected. The issue is when do this on your client that Excel will crash.

Check the section "How to handle a data validation alert" here:

http://office.microsoft.com/en-us/excel-help/apply-data-validation-to-cells-HP010072600.aspx#BM2_1

And we may try to clear the settings of Excel by following the steps:

http://support.microsoft.com/kb/280504

Best regards,

October 19th, 2012 12:29pm

Thank you again Rex,

I avoided the Excel has stopped working by removing the data validation list in my VBA when the user clicks on my 'save & new' button. I format the cell as text. This removes the 'Excel crashing issue' as they are not able to change the record anymore(record for me = the table row), hence double click on the listbox. So far so good :)

What remains is the issue with the (same) cell that now containins plain Text. It is still not possible to edit the contents in the cell because the text just disappears when double clicked on the cell. Pressing esc then shows the text again but when Enter is pressed or when I move to the next cell using the arrow keys, the text is removed. 

All works fine if I unprotect the sheet (please note that the cell is NOT locked. therefore editing the contents should be possible, not?).

Thank you beforehand.

Free Windows Admin Tool Kit Click here and download it now
October 23rd, 2012 9:31pm

On the Internet I found a similar question from someone "clicking in cells removes all previous text". Unfortunately there was no clear answer given. From the answers I tried the F2 'trick' but the result is is the same... clicking in cells removes all previous text.

October 23rd, 2012 9:53pm

anyone?

Thank you beforehand

Peter

Free Windows Admin Tool Kit Click here and download it now
October 27th, 2012 10:20pm

All idea about the "clicking in cells removes all previous text" issue are welcome.

Thank you.

November 5th, 2012 3:01pm

RE:  Double-click Excel cell with Data Validation List when worksheet is Protected

I know this is REALLY late in coming but, I had the same problem and discovered a workaround that worked for me.

WORKAROUND/SOLUTION:  check the {Edit Objects} checkbox when protecting the worksheet.

COMMENTS

  • I discovered the workaround by luck when I decided to see what happened if I checked ALL of the {Allow all users...} checkboxes when protecting the worksheet
  • As best I can determine, the {Edit objects} property only applies to objects such as Shapes, Images, etc.
  • I have no idea WHY it works
  • My environment = Win7Pro(x64)(w/SP1) + Office 2010 Pro (x86)

Hope this helps someone else...

Free Windows Admin Tool Kit Click here and download it now
February 6th, 2015 10:14am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics