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
Board Regular
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
Board Regular
Posted: Wed Jun 27, 2007 5:42 pm Post subject:
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
Board Regular
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
Board Regular
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
Board Regular
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
Board Regular
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
Board Regular
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
Board Regular
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.
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
Board Regular
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
Board Regular
Posted: Wed Jun 27, 2007 5:42 pm Post subject:
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
Board Regular
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
Board Regular
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
Board Regular
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
Board Regular
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
Board Regular
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
Board Regular
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.
