Protecting DropDown control from being moved or deleted

[Jim Williams]

Hello,
I'm using Excel 2010 in 2003 compatibility mode, with a Form Control DropDown on a protected worksheet, and the input range and cell link unprotected. All is well, but I cannot find a way to prevent a user from right-clicking on the control and either changing properties, moving or even deleting it. Ideally, there would be a way to protect the control.
The following VBA code:

Worksheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

correctly sets the property

Worksheets("Workbench").ProtectDrawingObjects

to True, but the control is still changeable, movable and deletable.
Any thoughts are most welcome.

Thanks!
Jim

 

[Andrew Poulsom]

Welcome to MrExcel.

Have you set the control's Locked property (right click, Format Control, Protection tab)?

 

[Jim Williams]

I'd been looking for the Protection tab but couldn't find it on the [Format Control] dialog. After thinking about your suggestion, I unlocked the sheet, then checked again. The Protection tab was now visible on the dialog. I checked "Protect", set sheet Protection on, and, voila! The control is properly protected. Strange dialog behavior (instead of graying out a tab), but now I know!

Thanks very much for helping me through a thorny puzzle.

Jim