macro run-time error '9': subscript out of range

Question

I found a macro on the web to protect a worksheet with a password. It works fine, but when I save the file I get the message: run-time error '9': subscription out of range. I have never programmed or used visual basic before and could use some help . Thank you

The macro is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1:Protect the sheet with a password
    Sheets("Sheet1").protect Password:="btfd"

'Step 2: Save the workbook
    ActiveWorkbook.Save
End Sub

Answer 1

"Subscript out of range" indicates that you've tried to access an element from a collection that doesn't exist. Is there a "Sheet1" in your workbook? If not, you'll need to change that to the name of the worksheet you want to protect.

Answer 2

Why are you using a macro? Excel has Password Protection built-in. When you select File/Save As... there should be a Tools button by the Save button, click it then "General Options" where you can enter a "Password to Open" and a "Password to Modify".

Answer 3

When you get the error message, you have the option to click on "Debug": this will lead you to the line where the error occurred. The Dark Canuck seems to be right, and I guess the error occurs on the line:

Sheets("Sheet1").protect Password:="btfd"

because most probably the "Sheet1" does not exist. However, if you say "It works fine, but when I save the file I get the message: run-time error '9': subscription out of range" it makes me think the error occurs on the second line:

ActiveWorkbook.Save

Could you please check this by pressing the Debug button first? And most important, as Gordon Bell says, why are you using a macro to protect a workbook?