Contextures Blog

Why 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.

Excel error

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.

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.

Revised Excel VBA Code

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, you can watch this short Excel video tutorial.

___________

This week, I’ve been working on a client’s Excel file, and we’re using a UserForm for data entry, instead of worksheet cells.

Data Entry and Storage

Data can be entered in the UserForm, and stored in a worksheet, when the form is closed.

The UserForm could open automatically when the file opens, or put a button on the worksheet, and click that to open the form.

On the Contextures website, you can find instructions and sample workbooks, for creating a simple UserForm, or a UserForm with drop down lists.

Watch the Excel UserForm Videos

To see the steps for creating an Excel UserForm, you can watch this 3-part Excel Video Tutorial series.

You’ll see how to add a UserForm to your Excel file, then put text boxes and buttons on the form.

Demo – Excel UserForm for Data Entry Demo

Creating a UserForm – Part 1

In part 1, you’ll see how to create a blank Userform. Then you’ll name the UserForm, and next you’ll add text boxes and labels.

Users will be able to type data into the text boxes. Labels are added beside the text boxes, to describe what users should enter into the text box

Creating a UserForm – Part 2

In Part 2, you’ll learn how to add buttons and a title on the UserForm.

With buttons on the UserForm, a user can click to make something happen.

For example, click a button after entering data in the text boxes, when you’re ready to move the data to the worksheet storage area

Creating a UserForm – Part 3

In Part 3, you’ll learn how to add VBA code to the controls, and you’ll see how to test the UserForm.

The VBA code runs when a specific event occurs, such as clicking a button, or entering a combo box. In this example, the user will click a button, and the VBA code will move the data to the worksheet storage area

Creating a UserForm – Part 4

In Part 4, you’ll see the code that adds the items to the combo boxes.

____________