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

Automatically Lock Certain Cells in Excel using a Macro & Password

0

I have to create a table where the users will input:

Entry # / Date / Sample ID / Result 1 / Result 2

then excel runs the calculations.

I want to lock the columns for "Result 1" and "Result 2" when the users enter data in these specific columns and be able to protect the worksheet with a password.

I found the following code by Don, it works great to lock those specific columns but I can't figure out how to add a password.

Thanks for any help.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 Or Target.Column = 5 And Target.Value <> "" Then

         ActiveSheet.Protect contents:=False

        Range(Target.Address).Locked = True

        ActiveSheet.Protect contents:=True

 End If

End Sub

excel
posted by: DuDu (rep: 2) on Sat Jun 5, 2021 at 1:13 pm
Answer
Discuss

Answers

0
Selected Answer

Hi DuDu and welcome to the Forum

Assuming you've unlocked cells (as Don's tutorial says), try this code instead to lock those cells (after an entry is made) with the sheet protected (changes shown in bold):

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Column = 4 Or Target.Column = 5) And Target.Value <> "" Then

        ActiveSheet.Unprotect "YrP@5Sw0Rd"

        Range(Target.Address).Locked = True

        ActiveSheet.Protect Password:="YrP@5Sw0Rd", Contents:=True, UserInterfaceOnly:=True

 End If

End Sub
but change the password in the code (used to unlock then re-lock).

Downside is that the password is in the code (but you can find ways of hiding the code or storing that elsewhere).

Not sure if that's what Don meant in the final paragraph of that tutorial but hope it works for you.

posted by: John_Ru (rep: 6152) on Sat Jun 5, 2021 at 2:39 pm
Discuss

Discussion

Thanks John_Ru
Works like a charm
DuDu (rep: 2) Jun 5, '21 at 4:03 pm
Great. Thanks for selecting my answer, DuDu. 
John_Ru (rep: 6152) Jun 5, '21 at 4:05 pm
DuDu

Looks like my Answer is no longer marked as Selected. Was that your choice for some reason (e.g. you had problems later)? 
John_Ru (rep: 6152) Jun 5, '21 at 4:27 pm
DuDu. Please respond...
John_Ru (rep: 6152) Jun 7, '21 at 8:45 am
Sorry about that.
Must have uncheck it by mistake.
DuDu (rep: 2) Jun 11, '21 at 5:19 pm
Thanks DuDu. Accidents do happen to all of us! 
John_Ru (rep: 6152) Jun 11, '21 at 6:21 pm
Add to Discussion


Answer the Question

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