Question re protection in Excel

  oo7juk 19:41 21 Jul 2007
Locked

Hi,

I've created a workbook that contains a combo box. The combo is populated with names and once selected populates other cells. Is it possible to protect workbook so that others users can only select the comobo box drop down feature and nothing else.

Thank you,

  evalesco 20:16 21 Jul 2007

Can you not use the ordinary Tools-> Protection -> protect Sheet ?

  oo7juk 20:36 21 Jul 2007

No, because it doesn't let you select the drop down menu within the combo box. That's the only cell I want others users to access.

Thanks.

  evalesco 20:46 21 Jul 2007

What version of excel are you using?

  oo7juk 20:51 21 Jul 2007

2003 evalesco

  Andy1991 21:00 21 Jul 2007

yes u can , :-)


Select the field for the combo box, go to the Data menu, ad Select Validation.

Change the "Allow" combo box an change it to list, then in the "Source" text box, type the first option then then a comma, then the second then a comma and so forth. Example below.

"Hello, Greetings, Hiya, Hi, Hola"

with out the speech marks.

Then on the right if u do want the combo box to be left blank then untick "ignore blank" then on the other tabs u can have other options like a tooltip to explain the ombo box and on the last tab u can choose what it says when they get it wrong. :-)

when done press OK.

Let me know how it works out for u.

  daba 22:42 21 Jul 2007

It is confusing because you say you have created a "combo" box, then later you say that's the only cell you want others to access.

A combo box is a VBA ActiveX component that sits ON the worksheet, not IN the worksheet, if you get my meaning.

Excel's own data validation for a cell entry will probably do what you want, without resorting to a VBA object. The interfacing between VBA and Worksheet data can be a bit clunky at times.

To do this without a VBA combo, put your list of names to select from on another sheet (or on the same one if you like in a hidden column, for example),

then in the "Data->Validation" criteria for the cell choose to "Allow" the "List" option, then enable "In-cell dropdown"

then in the "Source" box, simply select the cells with the list of names. Once you've created the link to the source list, you can hide the column with the source data.

Select All on the worksheet and apply the "locked" property, then select the drop-down cell, and clear the locked property for that cell only. Once the sheet is protected, only the one with the drop-down can be changed by the user.

  VoG II 09:56 22 Jul 2007

As a variation on Andy1991's suggestion, why use a combo box at all? To achieve what I think you want, you would be better off using Data > data Validation and in 'Allow:' select List on the drop-down. You can then specify a range that contains your list of allowed entries. In Excel 2003 this list must either be on the same sheet or can be a named range on another sheet. When you click in a cell a drop-down list of allowed entries will appear.

There is a tutorial click here 'Data Validation in Excel'.

  daba 16:01 22 Jul 2007

Sorry, VoG is absolutely right, you cannot use a cell-referenced range on another worksheet for you Data Validation source list. I typed my response without thinking about it or checking what I said.

But as he rightly notes, you can name the range of cells on the other sheet and use the named range as your validation criteria.

  oo7juk 22:49 22 Jul 2007

Hi,

Couldn't get the named range to work so just copied data into workbook and once I completed data valadation I just hid the col. Can the workbook be protected with the drop down option within the comobo box available for selection but nothing else available for editing.

Many thanks.

  Simsy 06:07 23 Jul 2007

You want one, and only one, cell available to have it's contents changed?

If so...

Select the sheet in question.
If it's currently protected then unprotect the sheet.
Ctl + A (to select everything)
On the "Format" menu choose "Cells"
On the "Protection" tab choose "Locked"
Select the cell you want, (which should mean only that cell is selected)
On the "Format" menu choose "Cells"
On the "Protection" tab UNTICK "Locked".
Protect the sheet.

This cell is now the only cell that can be edited, without "unprotecting" it.

You can now use data validation, as previously indicated, to provide the possible entries via a "drop down".

I hope this helps.

Regards,

Simsy

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

This is what design agencies will look like in 2032

How to update iOS on iPhone or iPad

WhatsApp : comment lire vos messages sans que l’expéditeur le sache