Locking all sheets with macro........works, but first sheet different than rest.

[jimbofoxman]

So I found lots of references to this kinda code for locking all sheets at once.

Sub ProtectAll()

    Dim wSheet As Worksheet
    Dim Pwd As String

    Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")

    For Each wSheet In Worksheets
        wSheet.Protect Password:=Pwd
    Next wSheet
    
End Sub

While it does lock all sheets, it doesn't lock them all the same. If I were to lock a sheet from the ribbon "Protect Sheet" button, the only two boxes I have checked are "Protect worksheet and contents of locked cells" and "Select Unlocked cells" from the lower check box list.

Then the user can only select and type the fields I have unlocked. They cannot select locked fields, say a formula cell.

When I use the macro, it locks the first page like above but all the rest of the sheets any cell can be selected. But even though any cell can be selected if it's locked they cannot change it..........so essentially it still is locked. A message pops up say You cannot change the cell as it's protected, blah blah blah. So it's locked.

It may be trivial, but I'd like it to lock all sheets like the first one, where only unlocked cells can be selected. I tried adding "Contents:=True" to the wSheet.Protect line but that didn't do anything to help.

Is their anything that can be done? Or is it just one of the downfalls of locking multiple sheets at once with code? In this case, probably 90 sheets.

Thanks,

Jim

 

[Scott T]

Try

Sub ProtectAll()
    Dim wSheet As Worksheet
    Dim Pwd As String
    Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
    For Each wSheet In Worksheets
        wSheet.EnableSelection = xlUnlockedCells
        wSheet.Protect Password:=Pwd
        
        
    Next wSheet
    
End Sub

 

[shg]

If you look at Help for the Protect method, there are lots of arguments to control what to enable and disable.

The problem you're having is that Excel remembers the protection settings for each sheet, and applies those as defaults when the sheet is reprotected.

 

[jimbofoxman]

Wow, thank you Scott T! That did the trick. I've done a lot of VBA stuff in Access, just not in Excel.

Thanks again!

 

[jimbofoxman]

Scott T's suggestion works great. I have 4 workbooks, each with roughly 90 tabs. We use them for our daily cash out's in our retail setting.

1Q18 (as in 1st Quarter 2018)
2Q18
3Q18
4Q18

I figured it wouldn't be a good idea to put the code in each workbook. Rather refer to one workbook. So after some google searching and youtube videos I found reference to using a workbook in XLSTART. Seems like a good idea, because every year we add 4 new workbooks to the mix for the new year. (Well, we actually clear the data and rename the files).

So I have the vba code as follows in the XLSTART workbook.

Sub ProtectAll()

    Dim wSheet As Worksheet
    Dim Pwd As String
    
    Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
    For Each wSheet In Worksheets
        wSheet.EnableSelection = xlUnlockedCells
        wSheet.Protect Password:=Pwd

    Next wSheet
    
End Sub

Sub UnProtectAll()

    Dim wSheet As Worksheet
    Dim Pwd As String
    
    Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
    
    On Error Resume Next
    
    For Each wSheet In Worksheets
        wSheet.Unprotect Password:=Pwd
    Next wSheet
    
    If Err <> 0 Then
        MsgBox "You have entered an incorrect password. All worksheets could not be unprotected.", vbCritical, "Incorrect Password"
    End If
    
    On Error GoTo 0

End Sub

But I figured out after calling the code from a ribbon button in one of the other workbooks, the code is protecting the sheets in the XLSTART workbook. I'm guessing as soon as I hit the ribbon button it now thinks the active workbook is the XLSTART workbook.

I'm guessing I need to pass a variable or something when executing the macro from the XLSTART workbook?

I saw references of doing some things like the following;

    Dim wbkCurrent As Workbook

    Set wbkCurrent = ActiveWorkbook
    
    wbkCurrent.Activate

So I guess I am trying to figure out how to make the code think the activeworkbook is the one I ran the code from.

I could see using this on these 4 workbooks as well as several others.

Any suggestions? Unfortunately I know more about MS Access than Excel.

Thank you

Jim

 

[MARK858]

Have you put the code in a regular module in the workbook you want the code to run on or in your Personal workbook?

If in a regular module in the workbook you want the code to run on what happens if you change every instance of ActiveWorkbbok to ThisWorkbook?

Please include changing lines like....

For Each wSheet In Worksheets

to

For Each wSheet In ThisWorkbook.Worksheets

 

[jimbofoxman]

Have you put the code in a regular module in the workbook you want the code to run on or in your Personal workbook?

If in a regular module in the workbook you want the code to run on what happens if you change every instance of ActiveWorkbbok to ThisWorkbook?

Please include changing lines like....

For Each wSheet In Worksheets

to

For Each wSheet In ThisWorkbook.Worksheets

I put the code in the Personal Workbook. Figured it was better there then say in 1Q18 workbook. Because eventually that workbook would cease to exist.

 

[MARK858]

Is your Personal Workbook hidden?

 

[jimbofoxman]

Is your Personal Workbook hidden?

Not currently, but probably will be as I don't need to see it everytime I run Excel.

 

[MARK858]

It should be hidden, the fact it isn't is probably why it is acting as the ActiveWorkbook.