Locking Cells problem

[Yusuf]

Hi all

Is it possible to lock cells with a Validation.List dropdown.
I'm having difficulty locking this cell and the hyperlink button when protecting the sheet.

Any suggestions would be appreciated

 

[Joe4]

I do not believe you can lock cells using Validation. You either have to do it manually, or use VBA to do it.

 

[Dufus]

Yusuf,

If you mean is it possible to lock a cell which has a validation list assigned to it, yes you can. It is possible that you have unchecked the option "Show error alert after invalid data is entered" in the Data Validation dialog box on the "Error Alert" tab. Unchecking this box will allow the user to enter information that is not in your list or doesn't meet your criteria.

Dufus

 

[Yusuf]

Thanks Jim

When you mention locking manually, do you mean through Format cells etc?
I've done this and it doesn't work.

Basically, I just need the cell that has a list drop down and the button to be inactive until the user enters a password to unprotect the sheet.

Is this possible?

 

[Joe4]

If you have the cell locked (through Format | Cells | Protection), and then Protect your worksheet, the cell with Data Validation will be uneditable. It will still show your Validation list, but you will not be able to make any selections/changes to that cell while the sheet is Protected.

BTW, who's Jim???

 

[Yusuf]

My apologies JM14

I have these cells linked to a spreadsheet that Vlooks various bits of data from other sheets in the workbook for this colleague.
There are seven sheets with the same layout, but in the validation list, the employee names are by each manager.

My task was to keep data hidden from each manager.

When I protect the sheets, after locking the cells and the hyp button, these objects are still active and selections can be made and the navigation button will open the totals page.

Is there another way I could do this?

 

[Yusuf]

Hi Dufus (it's not right calling you this)

Anyway, I tried what you suggested. But if I enter a colleagues name correctly, from another team when the sheet is unprotected, then it allows me to view this data.

I'll just about try anyone's suggestions at this point

Cheers
Y

 

[Joe4]

I don't think you can lock the hyperlink buttons.

If I were you, I would be tempted to try lock/protect/hides sheets using VBA, and get rid of the hyperlink button altogether. If you use VBA, you may be able to use a Command Button with VBA code behind it to give you the same result, but give you more control over it.