MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using a combo box in a protected work sheet


Posted by Denny on February 13, 2002 10:58 AM

I have a work sheet which is linked to four other work sheets. A combo box containing building sizes is used to gather information to fill in cells on sheet one. Every thing works find until I add protection to the work sheet. Now the combo box opens and when I click on a number I get the message telling me the work sheet is protected. Any ideas?

Thanks....


Posted by Juan Pablo G. on February 13, 2002 11:53 AM

Unlock the Linked cell.

Juan Pablo G.

Posted by Denny on February 13, 2002 12:09 PM

I guess I don't understand. I have unlocked the linked cells them protected the work sheet. Message saying work sheet protected persist, telling me to unlock work sheet. My goal is to update data by using the pull down box but not allow typing on the work sheet it self.

Again
Thanks for the help........

Posted by Juan Pablo G. on February 13, 2002 1:03 PM

What kind of combobox are you using ?

* Data Validation ?
* Forms toolbar ?
* Control toolbar ?

What version of Excel are you using ?

Juan Pablo G.

Posted by Denny on February 13, 2002 1:07 PM

I am working in Excel 2000 using the From Toolbar.

Posted by Juan Pablo G. on February 13, 2002 1:32 PM

I did this.

Created a new book, and all the cells, by default, are locked. Now, inserted a Form ComboBox, linked it to B1, and used A1:A3 as Data Range. (Sheet is still unprotected).

It works.

Now, I protected it, and I get an error (Because B1 is locked). I unprotect again, select B1, went to Cell Format, Protection, and unchecked "Locked", and then reprotected the sheet.

The combobox works now, with the sheet protected. So, one of us is doing something wrong here...

Juan Pablo G.

Posted by Denny on February 13, 2002 1:53 PM

Your right, one of us is doing something wrong and I bet anything it's me. Thanks for the help so far. I am going to reread your answer and keep trying till I get it right......LOL

Posted by Juan Pablo G. on February 13, 2002 2:07 PM

Just make sure that you're really unlocking the "data" cells that link to the controls, that's something I always forget.

Juan Pablo G.

Posted by Denny on February 13, 2002 6:36 PM

Juan,
You were correct, I was over looking a linked cell which was locked. Thank you for all the help.

Denny