Lock Sheet based on value of cell

[excelman]

Hi, i am looking to lock a sheet based on the value of a cell in shared workbook.

I have managed to achieve locking the sheet in my file, but it does not work when i share the workbook for others to update.

i want a sheet to lock based on the value of cell "C53" that will be updated by colleagues.

 

[chirayu]

You'll have to create a Worksheet Change Macro. Sample attached

 

[excelman]

You'll have to create a Worksheet Change Macro. Sample attached

Hi Chirayu,
tanks for our help, however the example still wont work if the file is on shared.
I have a code that works (see attached) but again, will not work once i share the worksheet for others to modify.

Private Sub Worksheet_Change(ByVal Target As Range)
If [C53] = "16" Then
ActiveSheet.Unprotect ("1")
[C3:C52].Locked = True
ActiveSheet.Protect ("1")
'Remove locked property if C53's value is anything else or is deleted.
Else
ActiveSheet.Unprotect ("1")
[C3:C52].Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect ("1")
End If
End Sub

 

[chirayu]

What do you mean by won't work? As in is it kept in shared drive? It should because I tested it on mine.

Are all the cells unprotected beforehand so user can use rest of sheet?

 

[Chihiro]

I think the issue is Protect/Unprotect Sheet will be greyed out and can't be changed when Workbook is "Shared" and allow changes by more than one user at the same time (not shared drive).

Might be better to use user form for data entry and have the sheet as xlveryhidden?

 

[chirayu]

Would make better sense yes. If multiple users are using the same file at the same time then it would cause issues.

 

[excelman]

Would make better sense yes. If multiple users are using the same file at the same time then it would cause issues.

Thanks for the help.

The code definitely works, but when i 'share' the sheet, i get a: "Run-time error '1004': Unprotect method of Worksheet class failed".

How do i apply the data entry solution?

 

[Chihiro]

Hi excelman,

That would depend on your network environment, criteria etc.

For an example, at work I have 7 different people entering data onto 7 different workbook. Strict data format is enforced by User Form (date picker, number validation, check box, combo box etc). Workbooks are stored in network shared drive.

Master workbook with data link to each of these workbooks is used to collect data on separate sheet for each workbook. I then use macro to combine it into single table to work with.