Protecting Cells on Unprotected sheet 1

[klmorgan]

Is there a way of password protecting cells on an unprotected sheet?

We have a sheet that five people have to authorise on a "round robin" the sheet is password protected to prevent any one else altering but we want to make it so that one person cannot authorise any one elses cell.

So person (a) can only enter into cell d5
person (b) can only enter in cell d6
etc
Regards

Keith

 

[KenWright]

You can have a macro pop up that prompts them for a password, and depending on the password given have it unprotect the relevant cells. If you protect the entire sheet first then even if they disable macros they will not be able to enter data in any cells.

the following was a simple bit of code to restrict access to specific sheets, but can be modified easily enough to do cells. Don't miss the bit at the bottom though as you need to hide your code if you don't want them seeing the passwords (Depends on whether you are actually trying to stop them accessing it, or doing something accidentally)


Sub NoPeekingNow()

Dim Ans As String

'Sheet has already been protected with password abcd
ActiveWorkbook.Unprotect ("abcd&quot
Ans = InputBox("Please input your password&quot

If Ans = "cdef" Then
Sheets("Sheet1&quot.Visible = True
Sheets("Sheet2&quot.Visible = True
Sheets("Sheet3&quot.Visible = True
Else
If Ans = "ghij" Then
Sheets("Sheet4&quot.Visible = True
Sheets("Sheet5&quot.Visible = True
Sheets("Sheet6&quot.Visible = True
Else: MsgBox ("Try Again, or Stop if you're not supposed to be trying&quot
End If
End If

ActiveWorkbook.Protect Password:="abcd"
End Sub

You will also need to hide/protect the VBA code, otherwise they could just go into the VBE ands see the passwords. In the VBE go to Tools, then VBA Project Properties, Protection Tab and you can enter a password for the code. I believe though that you have to close the workbook and then reopen it for it to take effect

If more than one person has access to the same file, ie you are not sending it out to them, but they are in fact accessing it on a shared drive, you would probably need to put an even macro in there to rehide the sheets Before Saving or Closing perhaps.

Regards
Ken..............