Locking cells

[Elfego Baca]

Can anyone tell me how to program a cell so that it locks automatically
when another cell is filled. For example I have a row with 5 cells,
A1,A2,A3,A4,A5. As soon as I enter anything in A1 I want cells A2,A3,A4
and A5 to lock and remain locked until the manager comes and enters a
password that allows that line to be unlocked. IS there a way to do this?

 

[Joerg]

Wouldn't a normal password protection of the sheet be sufficient in your
case? A1 unlocked, all other cells locked.

 

[Elfego Baca]

Wouldn't a normal password protection of the sheet be sufficient in your
case? A1 unlocked, all other cells locked.

No that is not it. If I were to password protect the sheet, then every
locked cell would be locked when the sheet was protected. I want B1, B2,
B3, B4 and B5 to be locked as soon as B1 is not blank. I then want I
want C1, C2, C3, C4 and C5 to be locked as soon as C1 is not blank. In
effect the entire row would be locked including the cell that was
filled. If the B row became locked, then the only way to unlock the row
would be for the administrator to enter a password the unlock that line.

 

[Joerg]

No that is not it. If I were to password protect the sheet, then every
locked cell would be locked when the sheet was protected. I want B1, B2,
B3, B4 and B5 to be locked as soon as B1 is not blank. I then want I
want C1, C2, C3, C4 and C5 to be locked as soon as C1 is not blank. In
effect the entire row would be locked including the cell that was
filled. If the B row became locked, then the only way to unlock the row
would be for the administrator to enter a password the unlock that line.

OK,understood. This differs from your original explanation, which didn't
include A1 in the cells to be locked.
I assume that entries in A2...A5 are possible until somebody enters
something in A1, which would cause the whole *column* (not line) to lock.
Means no possibility to correct an input error in A1. Is this correct?

 

[Joerg]

Following macro should be placed into the code section of ThisWorkbook. It
assumes that in a worksheet "Sheet1" the range A1:C5 is unlocked and that
the sheet is protected with password "Secret".

A user can enter data into range A2:C5, but when he enters a value into A1,
the range A1:A5 will be locked. Likewise, if he enters a value into C1,
then C1:C5 will be locked.

The macro needs finetuning and all caveats about the insecurity of password
protection apply, but since you asked, if there is a way...Yes, there is.

Cheers,
Joerg Mochikun

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Application.Intersect(ActiveCell,
Worksheets("Sheet1").Range("A1:C1")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect "Secret"
Range(ActiveCell, ActiveCell.Offset(4, 0)).Locked = True
ActiveSheet.Protect "Secret"
MsgBox "Column is now protected. To unprotect call the manager!"
End Sub

 

[Elfego Baca]

I have attempted to use the macro. First I opened a new excel
spreadsheet. I then highlighted the A1:C5 cells and changed them to
unlocked. I then went into the TOOLS|PROTECTION|Protect Sheet and
Protected the sheet using the Password "SECRET". I then hit ALT-F11 to
get into the code editor and entered the code exactly as you put it
under the THISWORKBOOK section. I then saved the sheet and tried
entering numbers in the B2-C5 section As soon as I try to enter anything
and press the ENTER KEY in this area I get a pop up error message that
states that there is a compile error: syntax error on the 1st and second
lines

The second line:"If Application.Intersect(ActiveCell," gets highlighted
and then when I press OK the first line"Private Sub
Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" became
highlighted in yellow with an arrow pointing to it. and the second line

Please tell me where the problem lies or what I am doing wrong.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Application.Intersect(ActiveCell,
Worksheets("Sheet1").Range("A1:C1")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect
Range(ActiveCell, ActiveCell.Offset(4, 0)).Locked = True
ActiveSheet.Protect
MsgBox "Column is now protected. To unprotect call the manager!"
End Sub

 

[Joerg]

Not your fault. My stupid newsreader inserted linebreaks when posting my
reply.
Please make sure that the second line reads "If
Application.Intersect...Then Exit Sub" - It must be one line.

Also please note that passwords are case sensitive, so if you use my example
it should be "Secret". (however you skipped the password from the code -
that's OK for testing, but then you shouldn't manually password protect your
sheet in the first place, otherwise your code can't unprotect it)

Joerg