Excel Efficiency: Fastest way to unprotect 100 worksheets in a workbook?

Factual Questions
1

I have Excel workbooks with dozens and dozens of protected worksheets. Sometimes I need to unprotect all of them to make a minor change, and then reprotect all of them again. They all have the same password. How I currently do that is:

  1. Type the password anywhere and copy it (ctrl+C).
  2. Click the first tab.
  3. Click Review
  4. Click Unprotect Sheet
  5. Press cntr+V to paste the password
  6. Press Enter
  7. Click the next tab
  8. Click Unprotect Sheet
  9. Press cntr+V to paste the password
  10. Press Enter
  11. Click the next tab
  12. etc, etc

There has to be a faster way to do this? Please teach me a method that uses fewer clicks and keystrokes. Thanks!

ETA: I will trade mouse clicks for key strokes. So if there are shortcut keys to bring up the Unprotect Password entry window, I’d rather do that than click. Or a way to go to the next tab using the keyboard rather than the mouse to click the tab. I think all the mouse movement slows it down the most. Especially because the “UNPROTECT SHEET” is at the top, and the tabs are at the bottom.

2

I think a macro will do it. Try:

Sub Protect()
Dim wsh As Worksheet
For Each wsh In Worksheets
wsh.Protect Password:=“yourpassword”
Next wsh
End Sub

Sub Unprotect()
Dim wsh As Worksheet
For Each wsh In Worksheets
wsh.Unprotect Password:=“yourpassword”
Next wsh
End Sub

You can also assign those two macros to key combinations

3

@OldGuy’s VBA script should work (assuming you have macros enabled, which is a security risk) but it is a bit of a kludge, and also codes in the worksheet password so you need to be careful about distribution. However, without intending to threadshit I have to ask if “Excel workbooks with dozens and dozens of protected worksheets” is really the right application for what you are doing. I’m assuming that the worksheets are password protected because there is an external user but that makes it really awkward to work with to manipulate data, and also prone to error if this is being done via a manual process.

You might want to consider migrating to a database or data management tool where the data can be securely managed and then exported to an Excel format. Of course, some companies and institutions have entire processes structured around using Excel with a lot of macros, conditional formatting, externally linked data sources, et cetera, and if that is the case you might just be locked into this situation, but if you can get away from using Excel in this way there are much better solutions. I say this having used Excel extensively and having to convert data processing and statistical analysis tools built in Matlab or Python into Excel, often with painful workarounds and running into intrinsic bugs in Excel.

Stranger

4

Just as one option to consider. It won’t be The Ultimate Time Saver, but it’s pretty convenient:

  1. Open the workbook
  2. Go to File > Info
  3. Locate the Protect Workbook section, which lists all protected worksheets
  4. Click Unprotect on the worksheets you want to change
5

I’m busy right now so can’t look it up, but you should be able to add a line before the “For Each …” which prompts you one time for the password then uses it in the For “loop”. This would remove the security risk of distributing the password.

6

Yeah, that would at least help with not incidentally exposing the password. Of course, it is almost trivially easy to bypass a password-protected worksheet in Excel so it is really just casual security intended to prevent someone from incidentally changing the worksheet.

Stranger

7

That only works in one direction (unprotecting), though.

You can separately protect the VBA code so that you have to enter a password to view it. You don’t need the password to run it, so it’s not like you have to enter that password every time, just to view it.

I think the VBA routine is the way to go, with the password in it, with the code protected.

8

Here’s what fifty bucks will buy you, incidentally. If this will be something you do fairly regularly, it may be worth considering (1m12s YT vid).

Quickly unprotect/protect multiple worksheets at once