Protect Shapes wile allowing doubleclick+data validation lists in same cell

rclark

New Member
Joined
Jun 24, 2015
Messages
27
I'm stuck. I can't find a way to protect my sheet while allow users to double click in a cell, use data validation list in the same cell while protecting shapes from being edited.
When I set DrawingObjects:=True the shapes are protected but I can no longer double click in a cell that has a data validation list in it.
When I set DrawingObjects:=False that is the only way I can get the double click to work in a cell with data validation but then my shapes can be edited
I created a macro on my shapes that shifts focus off of it which works for any left click efforts on the shape but all the right click functionality is still there.
I also created a macro that changed the protection settings when a cell that I needed to double click and have validation was selected but that logic doesn't really work because single clicking in the cell makes the shapes editable again. So I set a timer that when a doubleclick+validation cell was selected it would wait, doevents, then set back the fully protected settings but it was clunky.
Can someone help me with a way to pull this off?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
If you test data validation other than list, Excel allows user to double-click on the cell when sheet is protected as you describle.
But to amend a cell whose value is validated via a list, Excel expects user to click on the down arrow and select from the list.
So it is logical that Excel does not allow you to do what you are trying to do.

Why do you need to double-click on a cell validated via a list?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,215,045
Messages
6,122,836
Members
449,096
Latest member
Erald

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