Edit Object on Password Protected Worksheet

[dp724]

Hi guys,

I've searched long enough on getting around my issue, hoping someone can provide help me get this matter resolved. I have this password protected Excel template (v.2002), it includes one particular drop-down box, which is not a data validation choice selection, but a 'object' drop-down box which offers choices linked to a hidden value, via the INDEX function. The issue arises when I password protect the template and attempt to select from the available choices, I get a dialog box telling me the "the cell or chart...is password protected and...read-only"..."To modify...remove protection". I imagined checking the "Edit Objects" selection, upon protecting the worksheet would have avoided any problems, but that doesn't do the job.

Can someone please help me get this solved? Thanks in advance.

Dave

 

[Datsmart]

Can't tell from your post as to what your "drop-down box" is, a Forms Listbox or Combo Box, or a Controls Toolbar Listbox or combo box. In either case, you should be able to set the "Locked" properties to "Unlocked" so it will be usable when the sheet is protected.

 

[dp724]

Inserting and testing both 'Forms' and the 'Controls Toolbar' ComboBox options in to a blank worksheet, both appear to provide the same funtionality. In any case, I used the Controls Toolbar to insert the 'ComboBox' and the 'Properties' setting was set to 'Unlocked', since initial implementation. This property setting appears to have no significance, as it relates to my problem. Thanks for the thought and consideration.

Microsoft's site suggest removing the password protection to be able to edit the object, but it's a template and users will be prohibited from removing the protection.

 

[Datsmart]

Does your ComboBox attempt to make changes to cells that are unlocked or locked?
They will need to be unlocked to allow any change.

 

[dp724]

No, the combobox provides only a list of options.
As described...
The issue arises when I password protect the template and attempt to select from the available choices, I get a dialog box telling me the "the cell or chart...is password protected and...read-only"..."To modify...remove protection".

At this point, I must abandon any attempt to select from the combobox.

If you're not familiar with the following description:
"combobox options...linked to a value, via the INDEX function"
then I don't think you'd be able to help any further. But your help keeps me motivated, so I'm very much appreciative.

 

[Datsmart]

Yes, I have a test page setup using the INDEX function in an attempt to duplicate your issue.
I have noted that the referrence cell the ComboBox uses must be un-locked. Is your hidden "linked to a hidden value" cell un-locked?

 

[dp724]

Great! Now we're on the same page. I've tried all options, unlocking and unhiding all cells related to the combobox. Related cells include the text options, numerical values related to each text, the additional single value cell, which is used in the calculation displayed on the worksheet, and the cell which displays the calculated value. I still get the same dialog message.
When the worksheet is unprotected, the combobox works like a charm.
Hope I haven't lost you. I'm calling it a day, here in the office. Thanks so much!

 

[Datsmart]

Sounds like your situation is much more convoluted than my example page.
At any rate, any cell that has it's value changed by your combobox selection must be unlocked. Cells with Formulae that change do not need to be unlocked. Sounds like you have been pretty thorough in trouble shooting it. There must be some cell or range that is tripping up your project. Hope you can find it.

 

[dp724]

Please allow me to describe my set up, it's not too complicated at all. Once recreate it, you'll see the problem.

On a blank worksheet.

A1=$150
B1=A1*C3

A3=A (Text/General format)
A4=B (Text/General format)
A5=C (Text/General format)
A6=D (Text/General format)
A7=E (Text/General format)

A8=1 (General format)

B3=0% (Percent format)
B4=5% (Percent format)
B5=10% (Percent format)
B6=20% (Percent format)
B7=30% (Percent format)

C3=INDEX(B3:B7,A8)

Finally, using the 'Forms' toolbar (not 'Control Toolbox' toolbar), insert a combobox anywhere.
Set 'Format Control', 'Control' tab settings to:
Input range: $A$3:$A$7
Cell link: $A$8

Test the combobox and see how the (B1) value changes. Now, protect the worksheet and see my problem.

You guys have been great, thus far...I'm counting on you.

 

[Datsmart]

I did exactly as you kindly listed above.
If cell A8 is un-locked, all works fine when the sheet is protected.

Like I posted earlier:

Does your ComboBox attempt to make changes to cells that are unlocked or locked?
They will need to be unlocked to allow any change.

To Un-Lock a cell:
While your sheet is un-protected, right click cell A8.
Choose Fomat Cells...
In the Protection Tab, uncheck "Locked".
OK

Now protect your sheet, the combobox will work as designed.