Edit specific objects in protected work sheet

[L plates]

Hi All,

I've created a protected Excel 2010 staff scheduling work sheet that contains multiple form control buttons and one ActiveX combo box named "TempCombo". The combo box is part of an auto complete data validation list macro that I sourced from here:

http://www.contextures.com/xlDataVal11.html

This code is activated when users double click in an unprotected cell that has a data validation list. The code opens the TempCombo combo box in the target cell allowing auto completion of the validation list as they type. A great solution that is integral to the functionality of the sheet.

There is one caveat however in the use of this code in a protected worksheet - If the worksheet is protected you must allow users to Edit Objects to enable use of the combo box. The problem this creates is that it also allows all the form control buttons to be edited which defeats my reason for requiring a protected work sheet. The sheet originally used all ActiveX control (which had this issue beaten) but some bugs surfaced that meant I had to switch to form control. I started a thread about the issue on Excel Forum here:

1055146-command-button-text-enlarges-when-print-preview-is-exited

The only two solutions I can come up with are:

1) To access the ActiveX combo box from a hidden, unprotected sheet. I'm not really sure how I would rework the Contextures code to do this but it sounds feasible.
2) Protect the work sheet and only allow specific objects to be edited. i.e. the ActiveX combo box. Not sure if this is possible?

Users must not be able to edit the form controls in this work sheet. Any ideas would be greatly appreciated!

Many thanks,

[LJMetzger]

Hi Dave,

With the following code I was able to use both 'Active X' and 'Forms' ComboBoxes and CommandButtons (Excel 2003). I was also successful with 'Active X' TextBoxes. If the ComboBoxes (TextBoxes) were linked to a cell, the cell had to be unprotected. This should not be a problem, because the linked cell can be placed beneath a control.

Please Login or Register  to view this content.

Lewis

[yakamo]

Hi Sorry to bring this back from the past, how would i implement the code to the "TempoCombo" vb code so it can still be used when the sheet is protected?

cheers

Kev


Apologies, managed to find an answer to it. as per below instructions.

Note: If the worksheet is protected, allow users to Edit Objects, and they will be able to use the combobox.

cheers

Kev