Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Lock cell based on value of another cell

1

Hi, I want cell C to F of each row to lock if cell B value = "Completed"

I currently got it to work for 1 row only but I want this to repeat for every row.

The code I have so far

Thank you in advance everyone!


Private Sub Worksheet_Change(ByVal Target As Range)
'Lock Cell after verified
If [B1] = "Completed" Then
ActiveSheet.Unprotect ("PASSWORD")
[C1:F1].Locked = True
ActiveSheet.Protect ("PASSWORD")
'Remove locked property if B3's value is anything else or is deleted.
Else
ActiveSheet.Unprotect ("PASSWORD")
[C1:F1].Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If
End Sub
excelmacros-vba
MS Office Version: Excel 2007
posted by: MayDay1988 (rep: 4) on Sat Mar 25, 2017 at 8:42 pm
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

Please edit your question and put CODE tags around your code so it will be formatted like code (it makes it a lot easier to read).
cappymer1 (rep: 120) Mar 26, '17 at 3:45 am
Add to Discussion

Answers

1
Selected Answer

I haven't been able to test this but it should do what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
 
'Lock Cell after verified
If Target.Column = 2 Then

    If Target.Value = "Completed" Then
    
        ActiveSheet.Unprotect ("PASSWORD")
        Range(Cells(Target.Row, Target.Column + 1), Cells(Target.Row, Target.Column + 4)).Locked = True
        ActiveSheet.Protect ("PASSWORD")
        
    'Remove locked property if B3's value is anything else or is deleted.
    Else
    
        ActiveSheet.Unprotect ("PASSWORD")
        Range(Cells(Target.Row, Target.Column + 1), Cells(Target.Row, Target.Column + 4)).Locked = False
        'Optional, reprotect sheet
        ActiveSheet.Protect ("PASSWORD")
    
    End If
    
End If

End Sub

You just need to use the Target variable instead of hard-coding the cell references.

posted by: cappymer1 (rep: 120) on Sun Mar 26, 2017 at 3:44 am
Discuss

Discussion

Hi Cappymer1, thanks so much. I have very little knowledge about vba, hope you don't mind I'm asking this question. Do I need to replace anything in (Target.Row?, Target.Column)? As when I placed the code in the box it show text in red.

Thanks again
MayDay1988 (rep: 4) Mar 26, '17 at 3:54 am
You need to insert it into a worksheet and NOT a Module. But, it should copy/paste from here just fine.
cappymer1 (rep: 120) Mar 26, '17 at 4:05 am
This work perfectly. Thank you!
MayDay1988 (rep: 4) Mar 26, '17 at 4:57 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login