Edit Object on Password Protected Worksheet

D

dp724

Board Regular
Joined
Dec 16, 2006
Messages
63
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
Does your ComboBox attempt to make changes to cells that are unlocked or locked?
They will need to be unlocked to allow any change.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA & Combo Box (Form Control) Lock
Replies
3
Views
614
Jaafar Tribak
Jaafar Tribak
M
  • Question Question
Not running code
Replies
0
Views
191
MagicCarpet
M
G
  • Solved
VBA to allow grouping +- after reopening of the sheet
Replies
7
Views
1K
Greenbehindthecells
G
P
  • Solved
Existing macro to password protect sheets has stopped working
Replies
2
Views
1K
purplekombi
P
G
  • Question Question
Workbook formulas copied with Sheet references in new Workbook
Replies
0
Views
389
GK039
G

Forum statistics

Threads
1,223,276
Messages
6,171,139
Members
452,381
Latest member
Nova88

Share this page

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