Can I protect all excel tabs in a file with one password entry?

[Dave Peterson]

Did you put the code in the same workbook as the worksheets to protect?

Did you only run the first macro?

Were there any errors when you ran it?

 

[ktykerr]

Hi Dave,
Thanks for the quick reply.
I've put the code under the "thisworkbook" workbook alone.
I just ran the first macro and there were no errors.

After submitting my first post, I learned that the first sheet is protected
because I cannot touch/edit the data on the 1st worksheet itself. While on
the other worksheets I can still edit the data inside the cell that's why I'm
wondering.
I tried experimenting by deleting data on the next worksheets and after
pressing the “Enter†key it does prompted a message that the worksheet is
protected as well. So the code is working properly.
However, my next problem is that...after sharing the protected file the
users can “access/view†and “save as†the file as read only. After they have
saved the file as read only they were able to open/view the code through
Macro-Visual Basic Editor in effect they now can see the password.

Is there any way I can hide the macro code so that they won’t have access on
the password?
Also, is that a normal result that only the first sheet is fully protected
while you can still click and edit data the cells of the other worksheets?

 

[Dave Peterson]

Move the code to a general module
Insert|Module
and try it again.

You can protect your code by (inside the VBE):
Tools|VBAProject Properties|Protection tab
give it a memorable password.

Save and close your file and reopen it to test.

Hi Dave,
Thanks for the quick reply.
I've put the code under the "thisworkbook" workbook alone.
I just ran the first macro and there were no errors.

After submitting my first post, I learned that the first sheet is protected
because I cannot touch/edit the data on the 1st worksheet itself. While on
the other worksheets I can still edit the data inside the cell that's why I'm
wondering.
I tried experimenting by deleting data on the next worksheets and after
pressing the “Enter†key it does prompted a message that the worksheet is
protected as well. So the code is working properly.
However, my next problem is that...after sharing the protected file the
users can “access/view†and “save as†the file as read only. After they have
saved the file as read only they were able to open/view the code through
Macro-Visual Basic Editor in effect they now can see the password.

Is there any way I can hide the macro code so that they won’t have access on
the password?
Also, is that a normal result that only the first sheet is fully protected
while you can still click and edit data the cells of the other worksheets?

 

[ktykerr]

Hi Dave,
I tried it and it's working. Thank you for all the help.
Sorry for the late reply i can't send my post last time since the page is
prompting 'service is temprary not available'.
Thanks again.

 

[ladytiger7481]

I am looking to be able to do this as well. I was able to use the coding
above to lock all worksheets at one time but I haven't been able to add the
allow-users-to-edit-ranges.

Can someone please help me?

Does anyone know how to incorporate the allow-users-to-edit-certain-ranges
part into this protection macro?

Thank you!

 

[Dave Peterson]

Does the address of the range change for each sheet?

 

[David Corner]

Why not just use the build in "protect sheet" function? what benefit are you
looking for? cheers

 

[Gord Dibben]

Using the built-in "protect sheet" function requires that user goes to each
sheet individually and protects.

I think OP is looking for a simple way of doing all sheets at once.

Something like this.................

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Protect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Unprotect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 25 Aug 2009 07:45:03 -0700, David Corner <David

 

[SLD]

I copied the unprotect macroe

Sub Unprotect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="123"
Next ws
End Sub

and it is still giving me an error on the 4th line. the passwords are the
same on both macroes.

What am I doing wrong?

 

[Dave Peterson]

I'd bet that the password isn't really 123 for all the worksheets in the
workbook that contains the code.

Maybe you can add a line just to double check:

For Each ws In ThisWorkbook.Worksheets

msgbox "Processing: " & ws.name

ws.Unprotect Password:="123"
Next ws

That may give you a hint.

 

[Gord Dibben]

These two macros are what you are using?

Sub protect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="123"
Next ws
End Sub

Sub Unprotect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="123"
Next ws
End Sub

I see no reason why the unprotect won't work.


Gord Dibben MS Excel MVP

 

[SLD]

if i used the protect all ws macroe, would that not put the same password on
all ws?

 

[Dave Peterson]

Depends on if that worked correctly and if anybody/any code changed the
password.

I'd still bet that the password isn't what you think.

 

[SLD]

Where do I insert the line you sugest?

For Each ws In ThisWorkbook.Worksheets

msgbox "Processing: " & ws.name

 

[Dave Peterson]

right where I wrote it. Just above the line causing the trouble.

Then when/if the line fails, you'll remember the last worksheet name that was
shown to you. That's the one to check.

Where do I insert the line you sugest?

For Each ws In ThisWorkbook.Worksheets

msgbox "Processing: " & ws.name

 

[SLD]

This is working, but when you have over 100 tabs it is hard to keep track of
when the last mesege box is going to error out on me. Is there a line that
I can enter that will give me a report that i can clearly see what was the
last tab, or can it take me to the tab that does not have the correct
password?

 

[Dave Peterson]

Option Explicit
Sub Unprotect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
On Error Resume Next
ws.Unprotect Password:="123"
On Error GoTo 0
If ws.ProtectContents _
Or ws.ProtectDrawingObjects _
Or ws.ProtectScenarios Then
MsgBox ws.Name & " is still protected"
End If
Next ws
End Sub

 

[SLD]

Thanks so much. That worked. it helped me identify which tabs did have a
different password. This is a big help to me.

 

[SLD]

Is there a way to request a pasword to run the macroes?

 

[Gord Dibben]

What are you trying to achieve?

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
Dim pword as String
pword = InputBox("Enter a password to run this macro")
If pword <> "drowssap" Then Exit Sub
For N = 1 To Sheets.Count
Sheets(N).Unprotect Password:="123"
Next N
Application.ScreenUpdating = True
End Sub

Now you should lock the project from view.

Select workbook/project in VBE and Right-Click>VBAProject
Properties>Protection.

Lock and enter a unique password.

Do not forget this password..................you now have three different
passwords.

sheet protect...............123
inputbox...........drowssap
project properties...............unique word


Gord