Locking Cells problem

Yusuf

Active Member
Joined
Jun 1, 2004
Messages
337
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,843
Office Version
  1. 365
Platform
  1. Windows
I do not believe you can lock cells using Validation. You either have to do it manually, or use VBA to do it.
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
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

Active Member
Joined
Jun 1, 2004
Messages
337
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,843
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jun 1, 2004
Messages
337
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

Active Member
Joined
Jun 1, 2004
Messages
337
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,843
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,847
Messages
5,544,637
Members
410,627
Latest member
georgealice
Top