Activating in cell drop down list creates a VBA code execution error

Functional Environment:
MS Windows XP Professional Version 2002, Service Pack 3
MS Excel 2010

The problem I am encountering is that after a user has double clicked on a cell to activate in cell editing and then subsequently activates the in cell dropdown by clicking on the dropdown triangle, the VBA code gets errors where normally no errors occur.

In cell drop down settings:
Allow: List
Ignore blank - checked
In-cell dropdown - checked
Show input message when cell is selected - not checked
Show error alert after invalid data is entered - not checked

Sequence of events observed:

1. User activates in cell editing by double clicking on the cell.
2. User activates in cell dropdown by clicking on the in cell dropdown triangle.
3. As expected the "Worksheet_Change" event fires.
4. The line of code
   
   

   Please Login or Register  to view this content.

   failes with error "Run-time error '50290': Method 'ScreenUpdating of object '_Application' failed".
5. If I comment out that line of code then the next line of code
   
   

   Please Login or Register  to view this content.

   fails with error "Run-time error '50290': Method 'EnableEvents' of object '_Application' failed".
6. If that is commented out, some other lines of code work as expected but the program then fails again on this line of code
   
   

   Please Login or Register  to view this content.

   with error "Run-time error '50290': Application-defined or object-defined error'.
   In this instance I was able to determine the "object-defined" error is related to "ThisWorkbook.Names("general_task")". However, the "general_task" global range name actually does exist in the workbook but does not resolve to a recognizable object until after I halt code execution entirely in the debugger.

I have searched through a lot of Googled entries for problems other people have encountered referencing run time error 50290 but to no avail.

An interesting thing that I found out while compiling the above information is that if I check "Show error alert after invalid data is entered" and set "Style" to Stop. Whenever the user enters information that is not in the validation list, Excel allows the invalid data and does not warn the user. While this does not cause me any issues in this application, as I do allow information to be entered that is not in the list, I thought perhaps the root cause of this behavior may possibly be linked to what is causing the error that is causing me grief. However, I have not been able to make the logical connection between the two different problems yet.

Any ideas for solutions or insights into ways to troubleshoot this problem will be greatly appreciated.

Thanks,

Thank you for your suggestion.

I agree that the problem is likely because the cell is in edit mode. It could be a bug in Excel that allows the drop down to be selected while it is in edit mode, or a bug that the system cannot handle the termination of edit mode when the drop down is activated.

The inputbox suggestion may work but I have multiple other cells on the sheet that can be edited in cell that do not have drop downs and I'd like to avoid confusing the user by having a mixed input method system if possible as some of the end users will be a far cry from being power users.

Hopefully someone knows of a way to fix the issue so that selecting the drop down while in edit mode will not cause the errors.

Thanks again,

David

I have attached a copy of the workbook with stripped down sample data.

You can recreate the error by double clicking in any cell in the "Priority" column to activate in cell editing, then click on the drop down list triangle.

Thanks for looking at this for me.

David

I know this is an old thread but I came across it while trying to find a better solution to the same issue.

Either way this is how I use it in my workbook (but added the code to your Master Sheet):

Please Login or Register  to view this content.

Thanks, this helped lots. Have a nice 2021!