Thread: Enter Value in Cell - Unlocks Entire Row

Hi,

Can I enter a value into column A (any row) that would unlock the entire row whilst leaving the rest of the sheet protected?

Example - in A51 I would enter "Z" and that would unlock the row.

Thanks
Regards
Chris

Yes can be done with VBA. Not by formula. Want to go in that direction?

here's a start

Private Sub Worksheet_Change(ByVal Target As Range)

Dim isect As Range
Dim Ws As Worksheet

Set isect = Application.Intersect(Target, Range("A1:A100"))
If Not isect Is Nothing Then
  If Target.Value = "z" Then
    Target.Parent.Unprotect Password:="test"
    Target.EntireRow.Locked = False
    Target.Parent.Protect Password:="test"
  End If
End If
End Sub

Make all A1:A100 UNLOCKED (format cells protection)
Make B1:B100 LOCKED (format cells protection)

Put this code the the Sheet's code (right click TAB and view code)

Note that this will UNLOCK any row when you type z in that column. It does not change back (yet)

Hope you can manage, otherwise ... we'll be here.

Originally Posted by rwgrietveld

Yes can be done with VBA. Not by formula. Want to go in that direction?

Heh...that's probably why this Q is in the programming forum.

Zimbo,

1) Right-click the sheet tab and select VIEW CODE
2) Paste in these sheet-event macros.

The first will protect the sheet again when it is activated (brought up onscreen) but also set the flag that allows macros to change the protected worksheet even though it's protected. Edit the password.

The second is the macro that unlocks a row if "Z" is put in column A anywhere.

Option Explicit

Private Sub Worksheet_Activate()
    Me.Protect "password", UserInterfaceOnly:=True
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Target
    If cell.Column = 1 And UCase(cell) = "Z" Then Target.EntireRow.Locked = False
Next cell

End Sub

3) Close the editor
4) Save the workbook as a macro-enabled workbook
5) Switch to a different sheet, then come back...this triggers the first macro silently
6) You can now enter a Z in column A and the rest of the row will unlock.

This requires that you previously unlocked column A before the sheet was protected.

Go for JB's solution. It's more elegent as it includes
1) UserInterfaceOnly
2) It extends over the full range of column A and
3) If you Ctrl+Enter "Z" values in a few cells (in column A), JB's function will do them all.

Like so:

Option Explicit

Private Sub Worksheet_Activate()
    Me.Protect "password", AllowInsertingRows:=True, UserInterfaceOnly:=True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Target
    If cell.Column = 1 Then
        Select Case UCase(cell.Value)
            Case "Z"
                Cell.EntireRow.Locked = False
            Case Else
                Range("B" & Cell.Row, Cells(Cell.Row, Columns.Count)).Locked = True
        End Select
    End If
Next cell

End Sub

If you want to continue tweaking what is allowed and what is not on your protected sheet, turn on the macro recorder and let it record you protecting a sheet and setting all the flags the way you want them. Then look at that code to spot the items you need to add/subtract from the code.

I assume this means that column Q has to be left unlocked when the row locks again, yes?

Option Explicit

Private Sub Worksheet_Activate()
    Me.Protect "password", AllowInsertingRows:=True, UserInterfaceOnly:=True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Target
    If UCase(Cells(cell.Row, "A")) = "Z" Or UCase(Cells(cell.Row, "Q")) = "PC" Then
        cell.EntireRow.Locked = False
    Else
        Range("B" & cell.Row, "P" & cell.Row).Locked = True
        Range("R" & cell.Row, Cells(cell.Row, Columns.Count)).Locked = True
    End If
Next cell

End Sub

When you decide you need another expansion to this macro, take a look at the macro you have been given so far and try to adjust it yourself. Post up your attempt so I can help you work through it.

If you understand what the code is doing, you can learn and maintain it on your own, yes?

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)