unlock cell dependent on value entered in another cell
 

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

unlock cell dependent on value entered in another cell
 

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" wrote:

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

unlock cell dependent on value entered in another cell
 

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

"Mike H" wrote:

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" wrote:

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

unlock cell dependent on value entered in another cell
 

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: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.

---
Per

On 6 Mar., 13:20, johnsail wrote:
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



"Mike H" wrote:
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" wrote:

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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

unlock cell dependent on value entered in another cell
 

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?

"Per Jessen" wrote:

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: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.

---
Per

On 6 Mar., 13:20, johnsail wrote:
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



"Mike H" wrote:
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" wrote:

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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -