Protection

[mbarney]

I have a workbook using multiple tabs, all are password protected, I want to make a change that effects all tabs, can I unlock all tabs at one time?

[#2]

@mbarney 

You have to unprotect (and protect) them one by one. You could use macros for that:

Sub ProtectAll()
    Dim w As Worksheet
    For Each w In Worksheets
        w.Protect Password:="secret"
    Next w
End Sub

Sub UnprotectAll()
    Dim w As Worksheet
    For Each w In Worksheets
        w.Unprotect Password:="secret"
    Next w
End Sub

If you store these macros in your personal macro workbook Personal.xlsb, they will be available in all workbooks. You could assign the macros to Quick Access Toolbar buttons.

[#3]

So are you saying I have to un-protect "each" before writing this macro, than the macro after being written will un-protect all?

[#4]

@mbarney 

You can run the macro UnprotectAll to unprotect all sheets. The macro will do this one by one, but for you it's just one macro to run.

Similarly, if you want to protect all sheets, run the macro ProtectAll. Behind the scenes, this will protext them one by one.

[#5]

Got it, here's what I have, first time with macros.

Sub ProtectALL()
' Dim w As Worksheet
' For Each w In Worksheets
' w.Protect Password:=([123])
' Next w
'End Sub
'
'Sub UnprotectAll()
' Dim w As Worksheet
' For Each w In Worksheets
' w.Unprotect Password:=([123])
' Next w
'End Sub

Application.Run "PERSONAL.XLSB!Macro1"
Application.MacroOptions Macro:="PERSONAL.XLSB!Macro1", Description:= _
"Un-Protect All", ShortcutKey:="Q"
Application.Run "PERSONAL.XLSB!Macro1"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\mbarney\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB", _
FileFormat:=xlExcel12, CreateBackup:=False
Sheets("12-01-2021").Select
Application.Run "PERSONAL.XLSB!ProtectALL"
Sheets("12-01-2021").Select
ActiveSheet.Unprotect
Application.Run "PERSONAL.XLSB!ProtectALL"
Application.MacroOptions Macro:="PERSONAL.XLSB!ProtectALL", Description:= _
"Un-Protect All", ShortcutKey:="Q"
Application.Run "PERSONAL.XLSB!ProtectALL"
Application.Run "PERSONAL.XLSB!ProtectALL"
Sheets("11-30-2021").Select
End Sub

[#6]

Well, something isn't right, not working.

[#7]

@mbarney 

What are you trying to do?

[#8]

Duplicate your macro you provided to protect/unprotect the workbook.

[#9]

@mbarney 

Then you only need to copy/paste the code that I posted into a module.

[#10]

YOU ARE AWESOME! Got it to work just as I wanted, thanks much for all your help.