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...s_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" <[email protected]> wrote in message
news:[email protected]...
>
> Is there anyone that has dealt with this before?
>
>
> --
> Ph8te
> ------------------------------------------------------------------------
> Ph8te's Profile: http://www.excelforum.com/member.php...o&userid=36277
> View this thread: http://www.excelforum.com/showthread...hreadid=560573
>

[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 above.

I originally got the code from http://exceltips.vitalnews.com/Pages...s_by_User.html. The only thing I did was enter 31 worksheets for 31 individual Users. THank you for any help you can give me in advance I reallly apprciate the help

[Tim Williams]

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


"Ph8te" <[email protected]> wrote in message
news:[email protected]...
>
> Ph8te Wrote:
> > 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
> ------------------------------------------------------------------------
> Ph8te's Profile: http://www.excelforum.com/member.php...o&userid=36277
> View this thread: http://www.excelforum.com/showthread...hreadid=560573
>

[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


"Ph8te" <[email protected]> wrote in message
news:[email protected]...
>
> Ph8te Wrote:
> > 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
> ------------------------------------------------------------------------
> Ph8te's Profile: http://www.excelforum.com/member.php...o&userid=36277
> View this thread: http://www.excelforum.com/showthread...hreadid=560573
>

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.