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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to vba