Getting Excel Runtime Error 9: Subscript out of range

[Ph8te]

Hello all I am VERY new to the VB coding world and I am trying to create
a workbook with protectedworksheets. I used the code off of
http://exceltips.vitalnews.com/Pages/T0087_Protecting_Individual_Worksheets_by_User.html
The only difference is that I have about 31 worksheets.

I am able to open the workbook and enter the username password, BUT
when I click ok I get the Runtime Error 9: Subscript out of range
error. When I go to Debug it highlights in yellow this string:
Sheets(sSName).Visible = True

Is there anyone here that can help me out? THank you very much in
advance if anyone can figure this out.

 

[Ph8te]

well I was able to get rid of the Runtime 9 error NOW I am getting
multiple errors once I try to enter the Username and passwords.

I get 1004 errors now both when I try to enter the worksheet (says the
password is incorrct)

and when I try to exit the worksheet it gives me another 1004 error
Login error is
"Runtime error 1004" the password you have entered is incorrect, please
make sure that the CAPS LOCK is off and be sure to use the proper
capitilization.

when I click debug it pring me to this section highlighted in yellow
Sheets(sSName).Unprotect (txtPass.Text)

Then when I try to exit the Worksheet I get:
Runtime error 1004
Method 'Visible' of object '_Worksheet' failed when I click debug it
brings me here:
w.Visible = False

 

[Ph8te]

Is there anyone that has dealt with this before?

 

[Tim Williams]

Dealt with what?

Please *quote* what you're refering to. We're not all using "web forums" here...

Tim

 

[Ph8te]

well I was able to get rid of the Runtime 9 error NOW I am getting
multiple errors once I try to enter the Username and passwords.

I get 1004 errors now both when I try to enter the worksheet (says the
password is incorrct)

and when I try to exit the worksheet it gives me another 1004 error
Login error is
"Runtime error 1004" the password you have entered is incorrect, please
make sure that the CAPS LOCK is off and be sure to use the proper
capitilization.

when I click debug it pring me to this section highlighted in yellow
Sheets(sSName).Unprotect (txtPass.Text)

Then when I try to exit the Worksheet I get:
Runtime error 1004
Method 'Visible' of object '_Worksheet' failed when I click debug it
brings me here:
w.Visible = False

My appologies I forgot this is run off of Usenet.. please find my
question abaove.

 

[Ph8te]

Debug.Print the password and make sure it's what you expect it to be.

Not sure what would be causing the error on exiting - do you have event
code hooked up in the workbook ?

--
Tim Williams
Palo Alto, CA


message

Well I am still having the problem, the password when I move the cursor
over the error(testPASS.txt) IS correct and as I typed it. As for the
exiting error I am not sure where that is comming from...
here is a copy of something that is getting the same error when closing
the worksheet.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim w As Worksheet
Dim bSaveIt As Boolean

bSaveIt = False
For Each w In Worksheets
If w.Visible Then
Select Case LCase(w.Name)
Case "smith"
w.Protect ("horse01")
w.Visible = False
bSaveIt = True

Please help if you can not sure why I am getting either of these
errors.