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

3 Answers

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

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

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?

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy