asked on

Modify code to only lock certain excel sheets

How do I modify the following code in excel to only lock certain sheets, for example I want to only lock sheets "RFQ" and "QA_CLAUSES" in my workbook.

Sub ToggleProtection()
Dim oWs As Worksheet
Dim sPw

sPw = Application.InputBox("What's The Secret Sauce?", "Enter Password Skillit")
If sPw = False Or sPw = "" Then
MsgBox "Nothing Entered"
Exit Sub
End If

For Each oWs In ThisWorkbook.Worksheets
If oWs.ProtectContents Then
oWs.Unprotect sPw
Else: oWs.Protect sPw
End If

Next oWs
End Sub

Martin Liss

For Each oWs In ThisWorkbook.Worksheets
    Select Case oWs.name
        Case "A workbook name", "Another name'
            ' do something            
        Case Else
            'do something with the rest of the worksheets
    End Select
Next oWs

Open in new window

LUIS FREUND

ASKER

So under workbook name and another name I enter the "RFQ" and QA_CLUSES"...the sheet names?

Martin Liss

Yes, in line 3 you would add the sheets names in quotes for which you want to do something special, and one of them might be "RFQ". The default action defined by code that you place between lines 5 and 7 will be done to all the the other worksheets.

Martin Liss

I'd update my posted code but I don't know the names of the special worksheets and what you want to do to them.

SOLUTION

Anna bell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LUIS FREUND

ASKER

Basically wanted worksheet "RFQ" and "QA_Cluases" locked while the rest us not affected. I'm new to VBA...

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LUIS FREUND

ASKER

Thank you very much!

Martin Liss

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017