VBA to lock adjacent cells if not blank

[lander2]

Hello

An easy one I'm sure but I have not found a solution to this anywhere.

I've made a holiday tracker for staff to add their own leave, I've protected cells and added buttons including one the manager can click to enter the password and unlock for authorisation. Once holiday on a particular row has been authorised by the manager entering their name against that row, say in column G, I need the cell property for B to G on that row to be switched to locked. The protection gets turned back on when saved already so it's just locking those cells I can't do.

I've found the following code but not sure how to modify to include the full range e.g. B18:G50.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.Value <> "" Then
    ActiveSheet.Protect Contents:=False
Range(Target.Address).Locked = True
    ActiveSheet.Protect Contents:=True
End If
End Sub

Hope that makes sense and thanks for looking.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 7 And Target.Value <> "" Then
      Me.Unprotect "Pword"
      Intersect(Target.EntireRow, Range("B:G")).Locked = True
      Me.Protect "Pword"
   End If
End Sub

Change Pword to match your password

 

[lander2]

Thanks, I don't need password to be added here, l already got the workbook locking and unlocking working. Just need the property? (which is usually accessed via format cells) to switch to locked so those cells are protected on locking.

Thanks

 

[Fluff]

If the sheet is password protected you need to unlock it in order to change the locked property of the cells.

 

[lander2]

The manager clicks a button which asks for the password which, if correct, unlocks the workbook. That's working. I'm looking for code to the change cell properties of the row so that they lock once the manager has entered something into that row in column G. The workbook will be protected on save - that code is already working.

Thanks

 

[Fluff]

In that case just remove the Unprotect & Protect lines.

 

[Legacy 456129]

If you use in the protect parameters UserInterfaceOnly:=True you wont have to unlock most of the times. Certain Table/Listobjects actions though still needs it though and at reloading the workbook the protection with UserInterfaceOnly needs reassertion, so I made it part of the Workbook_Open procedure in the ThisWorkbook class module.

 

[lander2]

In that case just remove the Unprotect & Protect lines.

Thanks, I will have a look later and post back.

 

[lander2]

In that case just remove the Unprotect & Protect lines.

Oh wow,
Tried this and it works great.
Can you add a line to unlock cells b :c and e:f if the authorisation in column g is removed.

I've tried to adapt your code that do it but it just breaks.

Thanks

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 7 Then
      If Target.Value <> "" Then
         Intersect(Target.EntireRow, Range("B:G")).Locked = True
      Else
         Intersect(Target.EntireRow, Range("B:C,E:F")).Locked = False
      End If
   End If
End Sub