Unlock and Lock cells

[pells]

I am trying to write some code in Private Sub Worksheet_Change sheet module that will unlock and lock cells depending if they are blank or not.

I have got this far:

'to unlock H
If Not Intersect(Target, Range("B11:B81")) Is Nothing Then
ActiveSheet.UnProtect Password:="PASSWORD"
Cells(Target.Row, "H").Locked = False
ActiveSheet.Protect Password:="PASSWORD"
End If

'to lock H
If Intersect(Target, Range("B11:B81")) Is Nothing Then
ActiveSheet.UnProtect Password:="PASSWORD"
Cells(Target.Row, "H").Locked = True
ActiveSheet.Protect Password:="PASSWORD"
End If

So basically, if something is entered in the cell range B11 to B81, then unlock the cells H, if there is nothing in the call range B11 to B81 then lock the cells H.

What this seems to do is unlock and lock H at the same time and doesnt matter if the cell range is empty or not.

Has anyone got any ideas on how to resolve this please?

Many thanks for any suggestions supplied to my post.

 

[VoG]

Try like this

If Not Intersect(Target, Range("B11:B81")) Is Nothing Then
    If Target.Value <> "" Then
        ActiveSheet.Unprotect Password:="PASSWORD"
        Cells(Target.Row, "H").Locked = False
        ActiveSheet.Protect Password:="PASSWORD"
    End If
    Else
        ActiveSheet.Unprotect Password:="PASSWORD"
        Cells(Target.Row, "H").Locked = True
        ActiveSheet.Protect Password:="PASSWORD"
    End If
End If

 

[pells]

Try like this

If Not Intersect(Target, Range("B11:B81")) Is Nothing Then
    If Target.Value <> "" Then
        ActiveSheet.Unprotect Password:="PASSWORD"
        Cells(Target.Row, "H").Locked = False
        ActiveSheet.Protect Password:="PASSWORD"
    End If
    Else
        ActiveSheet.Unprotect Password:="PASSWORD"
        Cells(Target.Row, "H").Locked = True
        ActiveSheet.Protect Password:="PASSWORD"
    End If
End If

Many thanks for this.

I have tried it, but cell H is locked when cell B11 has contents and remains locked when cell B11 is empty.

Any other suggestions?

 

[VoG]

Sorry, I had an extraneous End If. Try

If Not Intersect(Target, Range("B11:B81")) Is Nothing Then
    If Target.Value <> "" Then
        ActiveSheet.Unprotect Password:="PASSWORD"
        Cells(Target.Row, "H").Locked = False
        ActiveSheet.Protect Password:="PASSWORD"
    Else
        ActiveSheet.Unprotect Password:="PASSWORD"
        Cells(Target.Row, "H").Locked = True
        ActiveSheet.Protect Password:="PASSWORD"
    End If
End If

 

[pells]

Sorry, I had an extraneous End If. Try

If Not Intersect(Target, Range("B11:B81")) Is Nothing Then
    If Target.Value <> "" Then
        ActiveSheet.Unprotect Password:="PASSWORD"
        Cells(Target.Row, "H").Locked = False
        ActiveSheet.Protect Password:="PASSWORD"
    Else
        ActiveSheet.Unprotect Password:="PASSWORD"
        Cells(Target.Row, "H").Locked = True
        ActiveSheet.Protect Password:="PASSWORD"
    End If
End If

Brilliant - many thanks, this works perfectly!

Once again, many thanks for all your help.

 

[pells]

Brilliant - many thanks, this works perfectly!

Once again, many thanks for all your help.

I think I spoke too soon...

If I run another macro in my workbook, this code stops working. Is there anything that needs to be added so that the code continues to work after other macros are run?

Many thanks.

 

[pells]

I think I spoke too soon...

If I run another macro in my workbook, this code stops working. Is there anything that needs to be added so that the code continues to work after other macros are run?

Many thanks.

Apologies, this does work, its me being stupid!

One thing that that this does though which is not ideal, is when an entry is made onto the cell H, the cursor doesnt jump to the next cell along i.e. cell I, but it jumps randomly to a cell at the bottom of my worksheet!

Does anyone know if this can be controlled as it doesnt make the sheet very workable?

Many thanks.

 

[VoG]

That code won't change the active cell. Is there more code that you haven't posted?

 

[pells]

That code won't change the active cell. Is there more code that you haven't posted?

I think I know what is happening. Because we have locked the cells apart from some, when enter is pressed, it cursor jumps down to the next unlocked cell. I understand excel default action for when the enter key is pressed its direction is down. I have recorded a macro and entered the code into the worksheet module to change the direction to right, which has seemed to resolve this little problem.

Again, many thanks for all your help.