Danny from Stuart Florida noticed that when you group all worksheets in an Excel workbook, you can not protect or unprotect the worksheets. This episode offers two very short VBA macros that you can use to unprotect all sheets and then re-protect them with a password.
Transcript of the video:
So in Excel, when you're in group mode, you can't protect or unprotect all of the sheets.
This question from Danny, who I met down in Stuart, Florida, when I was doing a seminar.
I was talking about group mode.
And he pointed out that when you're in group mode, the options back here on the review tab to protect sheet or unprotect sheet are grayed out. How annoying is that?
So he has a bunch of sheets.
He wants to unprotect them all so he can do some work on it, and then protect them all before he sends it out to his team.
So what we're going to do is we're going to ungroup and just start here from one sheet, and then we're going to go to the VBA window, which is alt-F-11.
And I find our workbook and I insert a module.
And then here in this blank module, we're going to have two different macros, one sub protect them all, open and close quote.
We're going to dim WS. It's a variable as a worksheet.
And then for each WS in active workbook.worksheets, WS.protect.
And if you want to add a password, add a password there.
Next, that'll make sure that it runs through all of them.
Let's try it. So we try and protect them all.
Good, worked without any hassle. We come back here.
This is the input cell that I can change, but if I try and change anything else, I get a message.
So we're effectively protected now. The opposite macro, unprotect them all.
Just change the WS.protect at WS.unprotect, same password.
Run that, and it will now allow me to type something in here.
Yes. Perfect.
It works. Okay.
Now, it doesn't really make sense probably to have this in the workbook that we're saving things.
It'll be best in your personal macro workbook.
So I'm going to take that module protect and just drag it down into the personal macro workbook.
Now that's beautiful. We don't need it here anymore.
And now any workbook that I have open in my personal macro workbook will offer me the choice to protect all or unprotect all.
So alt-F-8 and personal, protect them all. Click run.
They're all protected.
Or alt-F-8, personal, unprotect them all.
That's a little annoying, choosing from that long list.
I wonder if we can come up here, right click customize quick access toolbar.
If we go to macros, are the things in the personal macro workbook available? Yes, they are.
That would be the better way to go. So take protect them all.
Add, take unprotect them all, add. Change the icon of at least one of them.
So unprotect, I don't know. There's never enough icons here.
Click okay. All right.
So here we have protect them all. We'll protect everything.
And then here, unprotect them all. We'll unprotect.
Well, hey, thanks to Danny in Stuart for showing up on my live seminar last week.
And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
This question from Danny, who I met down in Stuart, Florida, when I was doing a seminar.
I was talking about group mode.
And he pointed out that when you're in group mode, the options back here on the review tab to protect sheet or unprotect sheet are grayed out. How annoying is that?
So he has a bunch of sheets.
He wants to unprotect them all so he can do some work on it, and then protect them all before he sends it out to his team.
So what we're going to do is we're going to ungroup and just start here from one sheet, and then we're going to go to the VBA window, which is alt-F-11.
And I find our workbook and I insert a module.
And then here in this blank module, we're going to have two different macros, one sub protect them all, open and close quote.
We're going to dim WS. It's a variable as a worksheet.
And then for each WS in active workbook.worksheets, WS.protect.
And if you want to add a password, add a password there.
Next, that'll make sure that it runs through all of them.
Let's try it. So we try and protect them all.
Good, worked without any hassle. We come back here.
This is the input cell that I can change, but if I try and change anything else, I get a message.
So we're effectively protected now. The opposite macro, unprotect them all.
Just change the WS.protect at WS.unprotect, same password.
Run that, and it will now allow me to type something in here.
Yes. Perfect.
It works. Okay.
Now, it doesn't really make sense probably to have this in the workbook that we're saving things.
It'll be best in your personal macro workbook.
So I'm going to take that module protect and just drag it down into the personal macro workbook.
Now that's beautiful. We don't need it here anymore.
And now any workbook that I have open in my personal macro workbook will offer me the choice to protect all or unprotect all.
So alt-F-8 and personal, protect them all. Click run.
They're all protected.
Or alt-F-8, personal, unprotect them all.
That's a little annoying, choosing from that long list.
I wonder if we can come up here, right click customize quick access toolbar.
If we go to macros, are the things in the personal macro workbook available? Yes, they are.
That would be the better way to go. So take protect them all.
Add, take unprotect them all, add. Change the icon of at least one of them.
So unprotect, I don't know. There's never enough icons here.
Click okay. All right.
So here we have protect them all. We'll protect everything.
And then here, unprotect them all. We'll unprotect.
Well, hey, thanks to Danny in Stuart for showing up on my live seminar last week.
And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
