unlock cell dependent on value entered in another cell

[johnsail]

Hi
I have a sheet where all cells are locked EXCEPT for one column.
For each row I then require that users enter a value in the one unlocked
cell and that this unlocks other cell on the same row.
Can this be done on a row by row basis?
Thanks

 

[Mike H]

Hi,

You don't provide too much to go on. This unlocks the active row if you
enter 99 in column A

Right click you sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Value = 99 Then
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="MyPass"
Rows(Target.Row).Locked = False
ActiveSheet.Protect Password:="MyPass"
Application.EnableEvents = True
End If
End If
End Sub

Mike

 

[johnsail]

Hi Mike
tried to respond earlier but I think it got lost in the cloud.

To be a little more specific:-
All cells locked except column A
If any value entered in A then C, D and E unlocked.
If then "Misc" selected from dropdown list in D - F is unlocked.
If any other value from the list in D is selected - K is unlocked.

Trying out your code has raised 2 other queries:-
1. Cell K contains a formula which is used in the "Misc" option above but is
overwritten by a user-entered value in the "other value" option.
If the value in D is deleted then the formula in K needs to be re-instated
and both F and K locked ready for a new entry to be selected in D.

2. If A is deleted then values in C,D,E and F also need to be deleted, the
formula re-instated into K and C,D,E,F and K locked.

Hope this is clear.

Thanks

John

 

[Per Jessen]

Hi John

The groups are not behaving normally today :-(

This is based on the code Mike posted earlier.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Not IsNumeric(Target) Then Exit Sub 'Allow Numeric values only
If Target.Value <> "" Then
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="MyPass"
Target.Offset(0, 2).Resize(1, 3).Locked = False
ActiveSheet.Protect Password:="MyPass"
Application.EnableEvents = True
Else
'Target =""
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="MyPass"
Target.Offset(0, 2).Resize(1, 4).Value = ""
Target.Offset(0, 2).Resize(1, 4).Locked = True
If Not Range("K" & Target.Row).HasFormula Then
Do
r = r + 1
Loop Until Range("K" & r).HasFormula
Range("K" & r).Copy
Range("K" & Target.Row).PasteSpecial xlPasteFormulas
End If
Range("K" & Target.Row).Locked = True
ActiveSheet.Protect Password:="MyPass"
Application.EnableEvents = True

End If
End If
If Not Intersect(Target, Range("D")) Is Nothing Then
If Target.Value = "Misc" Then
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="MyPass"
Target.Offset(0, 2).Locked = False
ActiveSheet.Protect Password:="MyPass"
Application.EnableEvents = True
ElseIf Target.Value <> "" Then
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="MyPass"
Target.Offset(0, 7).Locked = False
ActiveSheet.Protect Password:="MyPass"
Application.EnableEvents = True
End If
End If
End Sub

Hopes this helps.

 

[johnsail]

Hi Per

Thanks for the code - I am trying to work my way thru it and understand
whats going on and refining it as I go!! This is taking some time as you can
imagine.

One little problem:
when the code is actioned to unlock a cell or series of cells the cursor
moves to the unlocked cell on the next line. How do I get it to move to the
cell that has just been unlocked (or the first cell of a multiple unlock) on
the active line?