0 votes
in VBA by Beginner (18 points)

Hello Team

How do I write a code which can protect/unprotect only specific worksheets in a workbook?

I need to protect and unprotect sheets with a name starting with "Prepaid"  as part of the code! while other worksheets should not have any effect at all. 

Your help is very much appreciated as usual 



1 Answer

+1 vote
by Super Expert (3.2k points)
selected by
Best answer

To protect and unprotect sheets with a specific name using VBA, run a macro like this one:

Sub ToggleProtection()
'Toggles protection on and off for any sheet beginning with "Prepaid"
'  Assumes there are no passwords on the sheets.
Dim sh As Worksheet
Dim iCount As Integer
Dim bProtection As Boolean
For Each sh In ActiveWorkbook.Worksheets
    If UCase(Left(sh.Name, 7)) = "PREPAID" Then
        iCount = iCount + 1
        If iCount = 1 And sh.ProtectContents = True Then bProtection = True
        If bProtection = False Then
            'protect the sheets
            'unprotect the sheets
        End If
    End If
Next sh
End Sub

This macro loops through each sheet in your workbook and toggles the protection status. If the sheet name begins with "Prepaid" (case-insensitive), it will toggle protections on or off based on the protection status of the very first "Prepaid" sheet it fines. If the first sheet with your naming convention is protected, that sheet and all subsequent sheets will be unprotected. If it's already unprotected, that sheet and all subsequent sheets will be protected. That way, you can guarantee all the "Prepaid" sheets will have the same protection status.

This macro assumes there are no passwords on the protected sheets and it applies the default protection.

by Beginner (18 points)

Thank you  wellsr Super Expert yes

I will try the code in my application and let you know if it worked! 

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.


For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.