Locking Cell if another Cell has Text

[moxiepilot]

Good morning,

The spreadsheet I am working on needs the community's assistance.

The goal would be to Lock 3 cells in the same row IF any alpha-numeric character is input in another cell. For example, if the user marks cell B12 - then lock cells C12, D12 and E12; if the user marks cell C12 then lock B12, D12 and E12.

If the user made a mark in error, they need to be able to clear the cell mark and choose one of the 4 cells.

I would like this option for about 50 unique rows, to lock the cells in each row when a mark is made.

Is there a way to do this?

I'm not savvy with VBA, but I know how to view code and type it in

Any help or assistance is greatly appreciated.

 

[Joe4]

Welcome to the Board!

In order to do something like this, you will need to use sheet protection. So here are some preliminary steps you will need to run:
1. Highlight all the cells in your range (B1:E50) or whatever your range is, and go to Format Cells, then go to the Protection tab and uncheck the "Locked" box.
2. Password protect your sheet (and insert the password in the code below).

Now, right-click on the sheet tab name at the bottom the screen, select View Code, and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim prtect As Boolean

'   Only run if one cell us updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run if update is cell updated is in range B1:E50
    If Intersect(Target, Range("B1:E50")) Is Nothing Then Exit Sub
    
'   See if updated target cell has a value (if it does, lock other cells, if it does not, unlock other cells)
    prtect = (Target <> "")
'   Unprotect sheet
    ActiveSheet.Unprotect Password:="password"
'   Lock/unlock cells in columns B-E
    Range(Cells(Target.Row, "B"), Cells(Target.Row, "E")).Locked = prtect
'   Unlock target cell
    Target.Locked = False
'   Re-protect sheet
    ActiveSheet.Protect Password:="password"
    
End Sub

As you enter a value in the range B1:E50, it will lock the other columns in that particular row.
As you remove/delete it, it will unlock the other columns in that particular row.

 

[moxiepilot]

Welcome to the Board!

Thank you Joe. I will try that out and let you know how it works!

-moxie

 

[moxiepilot]

I have a pretty big grin on my ugly mug. Thanks for the assistance, it appears to do exactly what I was looking to accomplish.

I modified the range and didn't break it - so I appreciate the template and have to credit you with me looking good to one of my peers.

:cheers:

 

[Joe4]

You are welcome! Glad I was able to help!

I documented it so hopefully you would be able to modify it to suit your needs, and it sounds like you successfully did that.