Lock and unlock sheet via VBA coding

Question

I've modified a VB code to show and hide rows based on the value of a cell. The code works well, but I need to password protect the sheet, and of course once the sheet is protected the VB won't run.

I've tried a few variations but I'm not a programmer so I'm just not getting it - could someone smarter please help me out?

Code as below:

Private Sub Worksheet_Change(ByVal Target As Range)

 Rows("1:" & Rows.Count).EntireRow.Hidden = False

 If Range("M11") = "0" Then
 Rows("13:92").EntireRow.Hidden = True

End If
 
 If Range("M11") = "Mini" Then
 Rows("13:18").EntireRow.Hidden = True
 Rows("38:57").EntireRow.Hidden = True
 
 End If

 If Range("M11") = "Small" Then
 Rows("13:18").EntireRow.Hidden = True
 Rows("43:57").EntireRow.Hidden = True
 
 End If

 If Range("M11") = "Standard" Then
 Rows("13:18").EntireRow.Hidden = True

 End If
 
End Sub

I tried variations of unlock code, but I don't know enough about programming to know how it all fits together, so I just get errors.

Use Worksheet.Protect and set UserInterfaceOnly = True. The macros will be able to make changes to the worksheet but it will be locked against user editing. — Toddleson
– Toddleson, Commented Nov 16, 2022 at 14:30

BigBen · Accepted Answer · 2022-11-16 14:40:21Z

2

Unprotect, hide/unhide based on the cell value, then re-protect.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("M11")) Is Nothing Then Exit Sub

    Me.Unprotect Password:="YourPasswordHere"
    Me.Rows.Hidden = False

    Select Case Me.Range("M11").Value
        Case 0
             Me.Rows("13:92").Hidden = True
        Case "Mini"
             Me.Rows("13:18").Hidden = True
             Me.Rows("38:57").Hidden = True
        Case "Small"
             Me.Rows("13:18").Hidden = True             
             Me.Rows("43:57").Hidden = True
        Case "Standard"
             Me.Rows("13:18").Hidden = True
    End Select

    Me.Protect Password:="YourPasswordHere"
End Sub

As mentioned in the comments, another option is to Protect the sheet using UserInterfaceOnly:=True. With this method, you'll need to leverage the Workbook_Open event to protect the sheet. UserInterfaceOnly does not persist if the workbook is closed.

edited Nov 16, 2022 at 14:40

answered Nov 16, 2022 at 14:33

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KarenW Over a year ago

Thank you, BigBen, but when I use this code nothing happens - the row hide functionality just completely stops.

BigBen Over a year ago

In the Immediate Window, type ? Application.EnableEvents and hit Enter. What is the result? Did you also replace "YourPasswordHere" with your actual password?

KarenW Over a year ago

When I add that code and hit Enter it adds "True" as a second line. I did replace the password, but nothing.

BigBen Over a year ago

Next step might be to add some Debug.Print statements to see what code is executed.

Collectives™ on Stack Overflow

Lock and unlock sheet via VBA coding

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related