When Good Excel Macros Go Bad

iconmacrosWhy do good Excel macros suddenly go bad? Does that ever happen to you? Here’s my latest adventure, and maybe it will help you prevent (or solve) a similar problem.

Unprotect Sheets With a Macro

When making changes to client files, I use macros to quickly and easily protect or unprotect all the sheets in an Excel file, like the examples shown below.

  • This macro protects all the worksheets in the active workbook, with no password.
Sub ProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets
  ws.Protect DrawingObjects:=True, _
    Contents:=True, Password:=""
Next ws

End Sub
  • And this macro unprotects all the worksheets, with no password.
Sub UnProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets
  ws.Unprotect
Next ws

End Sub

The Protect Macro Fails

However, even a simple macro can run into problems, as I’m sure you’ve already discovered with your own macros. It worked yesterday, but the macro won’t run correctly today, in the same file. And you’re sure that nothing was changed in the file – so what’s the problem?

That’s what happened to me recently, while trying to unprotect a file, so I could make some changes. The macro ran, but the sheet’s weren’t unprotected.

I commented out the macro’s error handling, to try and solve the problem, and this run-time error 1004 popped up:
Method ‘Unprotect’ of object ‘_Worksheet’ failed.

protecterror02

Prevent the Macro Problem

After a bit of head scratching, I realized what the problem was – I had grouped some of the sheets, because they all needed a formatting change. Oops! Because the sheets were grouped, they couldn’t be unprotected.

I added a line of code to the macro, to ungroup the sheets, if necessary, and the problem is solved. You could do something fancier, like identifying the active sheet, and selecting it, instead of the first sheet.

Sub ProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next
Sheets(1).Select '<====== selects first sheet in workbook
For Each ws In ActiveWorkbook.Worksheets
  ws.Protect DrawingObjects:=True, _
    Contents:=True, Password:=""
Next ws

End Sub

The same line was added to the Unprotect macro.

Sub UnProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next
Sheets(1).Select '<====== selects first sheet in workbook
For Each ws In ActiveWorkbook.Worksheets
  ws.Unprotect
Next ws

End Sub

Watch the Protect Macro Fails Video

To see the problem that occurs when you try to protect or unprotect group sheets, and the code change that fixes it, please watch this short Excel video tutorial.

___________

You may also like...

6 Responses

  1. Doug Glancy says:

    Excel should have a blinking icon or frequent popup whenever more than one sheet is selected. A couple of times lately I’ve broken out in a sweat upon realizing I kept sheets grouped and blithely continued working, making who-knows-what-unintended changes.

  2. Fakhar Hussain says:

    Dear Debra,
    I have excel 2007,what is add-in tab and what are its advantage/features and how can I add it in the ribbon.

    Second, when I open a file with macros I get error message that “macros have been disabled” and have to enable them every time I open the file, please advise how to fix the error?

    Thanks and regards
    Fakhar

  3. Jennifer Deacon says:

    I’ve had similar problems in the past too, I second the comment around making the fact that sheets are groups should be more obvious.

  4. Robert Ryan says:

    I third the first comment. A couple of months ago, I confidently thought I would be able to help someone do something fairly straightforward, but I wound up frustrated and perplexed. Sure enough, someone later found it wouldn’t work because two sheets were grouped.

  5. Agreed – being able to group sheets is the best and worst feature of Excel at times. I’ve shot myself in the foot over and over with that feature, continuing to make edits long after the need for the grouping is over…doh! You’d think that I would learn…

    Assuming this macro is used on a workbook before distribution, selecting the first worksheet is probably a good “feature” as well as a way to prevent the macro error. That way, the same sheet is always selected before you distribute the workbook to your customers…

Leave a Reply

Your email address will not be published. Required fields are marked *