Code to block entire line, if cell is filled

[Danikuku]

Hi everyone,

I have a problem: I have an excel file that is shared by a number of people: First is filled by one person and then validated by some others (with their names in a combobox and password). Whenever the last person validates (column F), I would like to block the entire row (A:AF).

I am using the following code that I have adapted:

Private Sub block (ByVal Target As Range)
If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub

If Target.Value <> "" Then
Range("a" & Target.Row, "af" & Target.Row).Locked = True
Else
Range("a" & Target.Row, "af" & Target.Row).Locked = False
End If

End Sub

Nevertheless, this doesn't work. Is it the code, or maybe because the file is protected? Or even because the cells are unlocked (format cells > protection > unlock)?

Thank you for your help.

 

[FormR]

Welcome to the forum!

You can't change the name of the sub if you are using event based code, also if your sheet is protected you will need to un-protect and then re-protect the sheet to change the locked property.

Give this a try, change "thepassword" to your actual password.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub
Me.Unprotect "thepassword"
If Target.Value <> "" Then
    Range("a" & Target.Row, "af" & Target.Row).Locked = True
    Else
    Range("a" & Target.Row, "af" & Target.Row).Locked = False
End If
Me.Protect "thepassword"
End Sub

 

[Danikuku]

Welcome to the forum!

You can't change the name of the sub if you are using event based code, also if your sheet is protected you will need to un-protect and then re-protect the sheet to change the locked property.

Give this a try, change "thepassword" to your actual password.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub
Me.Unprotect "thepassword"
If Target.Value <> "" Then
    Range("a" & Target.Row, "af" & Target.Row).Locked = True
    Else
    Range("a" & Target.Row, "af" & Target.Row).Locked = False
End If
Me.Protect "thepassword"
End Sub

Uau, thank you! It works perfectly!
I'm really new at this, so thank you for your help.

 

[Danikuku]

Uau, thank you! It works perfectly!
I'm really new at this, so thank you for your help.

I'm sorry again.
Just noticed a bug. It only locks from a:V instead of locking A:AF!
I can't understand why!

 

[FormR]

Hi again, it locks all the way to column AF for me. Did you change the code at all?

 

[Danikuku]

Hi again, it locks all the way to column AF for me. Did you change the code at all?

I don't think so... just added the password!

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub
Me.Unprotect "comp"
If Target.Value <> "" Then
    Range("a" & Target.Row, "af" & Target.Row).Locked = True
    Else
    Range("a" & Target.Row, "af" & Target.Row).Locked = False
End If
Me.Protect "comp"
End Sub

 

[FormR]

I don't think so... just added the password!

Very strange! If I was writing it from scratch I would do it like this; functionally the only difference is that it won't fall over if multiple cells in column F are changed at the same time so I doubt it will solve your problem - but worth a shot.

Private Sub Worksheet_Change1(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Columns("F")) Is Nothing Then
    Me.Unprotect "comp"
    For Each c In Intersect(Target, Columns("F"))
        Range("A" & c.Row & ":AF" & c.Row).Locked = c.Value <> ""
    Next c
    Me.Protect "comp"
End If
End Sub

 

[Danikuku]

Well, thank you anyway!

 

[FormR]

Well, thank you anyway!

If you are still having problems, you could upload your workbook to a free file sharing site (like dropbox) and share the link here - I'm sure someone would be willing to take a look for you.