Runtime Error '9'
 

I am using Win XP, Excel 2003.

I receive a "Runtime Error '9': Subscript out of range" message.

During the debug it highlights Sheets(sSName).Visible = True

The sheet names are Corp and Elect.

What I am trying to do is have the user enter the Name and password on a
userform to access a specific worksheet.

What is the problem?

Runtime Error '9'
 

There's no sheet named with the value of sSName ?

--
Tim Williams
Palo Alto, CA


"EbonLinctus" wrote in message ...
I am using Win XP, Excel 2003.

I receive a "Runtime Error '9': Subscript out of range" message.

Code:

---

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
    Dim bError As Boolean
    Dim sSName As String
    Dim p As DocumentProperty
    Dim bSetIt As Boolean

    bOK2Use = False
    bError = True
    If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
        bError = False
        Select Case txtUser.Text
            Case "user1"
                sSName = "Corp"
                If txtPass.Text < "u1pass" Then bError = True
            Case "user2"
                sSName = "Elect"
                If txtPass.Text < "u2pass" Then bError = True
            Case Else
                bError = True
        End Select
    End If
    If bError Then
        MsgBox "Invalid User Name or Password"
    Else
        'Set document property
        bSetIt = False
        For Each p In ActiveWorkbook.CustomDocumentProperties
            If p.Name = "auth" Then
                p.Value = sSName
                bSetIt = True
                Exit For
            End If
        Next p
        If Not bSetIt Then
            ActiveWorkbook.CustomDocumentProperties.Add _
              Name:="auth", LinkToContent:=False, _
              Type:=msoPropertyTypeString, Value:=sSName
        End If

        Sheets(sSName).Visible = True
        Sheets(sSName).Unprotect (txtPass.Text)
        Sheets(sSName).Activate

        bOK2Use = True
        Unload UserForm1
    End If
End Sub

Private Sub UserForm_Terminate()
    If Not bOK2Use Then
        ActiveWorkbook.Close (False)
    End If
End Sub


---

During the debug it highlights Sheets(sSName).Visible = True

The sheet names are Corp and Elect.

What I am trying to do is have the user enter the Name and password on a
userform to access a specific worksheet.

What is the problem?

Runtime Error '9'
 

No. As you can tell, I copied the code from Vital News' website.

The original code follows is as follows

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
Dim bError As Boolean
Dim sSName As String
Dim p As DocumentProperty
Dim bSetIt As Boolean

bOK2Use = False
bError = True
If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
bError = False
Select Case txtUser.Text
Case "user1"
sSName = "u1sheet"
If txtPass.Text < "u1pass" Then bError = True
Case "user2"
sSName = "u2sheet"
If txtPass.Text < "u2pass" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate

bOK2Use = True
Unload UserForm1
End If
End Sub

Private Sub UserForm_Terminate()
If Not bOK2Use Then
ActiveWorkbook.Close (False)
End If
End Sub

So...what do I need to do to obtain the result I want?


"Tim Williams" wrote:

There's no sheet named with the value of sSName ?

--
Tim Williams
Palo Alto, CA


"EbonLinctus" wrote in message ...
I am using Win XP, Excel 2003.

I receive a "Runtime Error '9': Subscript out of range" message.

Code:

---

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
    Dim bError As Boolean
    Dim sSName As String
    Dim p As DocumentProperty
    Dim bSetIt As Boolean

    bOK2Use = False
    bError = True
    If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
        bError = False
        Select Case txtUser.Text
            Case "user1"
                sSName = "Corp"
                If txtPass.Text < "u1pass" Then bError = True
            Case "user2"
                sSName = "Elect"
                If txtPass.Text < "u2pass" Then bError = True
            Case Else
                bError = True
        End Select
    End If
    If bError Then
        MsgBox "Invalid User Name or Password"
    Else
        'Set document property
        bSetIt = False
        For Each p In ActiveWorkbook.CustomDocumentProperties
            If p.Name = "auth" Then
                p.Value = sSName
                bSetIt = True
                Exit For
            End If
        Next p
        If Not bSetIt Then
            ActiveWorkbook.CustomDocumentProperties.Add _
              Name:="auth", LinkToContent:=False, _
              Type:=msoPropertyTypeString, Value:=sSName
        End If

        Sheets(sSName).Visible = True
        Sheets(sSName).Unprotect (txtPass.Text)
        Sheets(sSName).Activate

        bOK2Use = True
        Unload UserForm1
    End If
End Sub

Private Sub UserForm_Terminate()
    If Not bOK2Use Then
        ActiveWorkbook.Close (False)
    End If
End Sub


---

During the debug it highlights Sheets(sSName).Visible = True

The sheet names are Corp and Elect.

What I am trying to do is have the user enter the Name and password on a
userform to access a specific worksheet.

What is the problem?

Runtime Error '9'
 

There is no sheet in the activeworkbook named whatever sSName holds.

If you're positive that there is, then look for typing differences--leading or
trailing spaces may be the culprit.



EbonLinctus wrote:

No. As you can tell, I copied the code from Vital News' website.

The original code follows is as follows

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
Dim bError As Boolean
Dim sSName As String
Dim p As DocumentProperty
Dim bSetIt As Boolean

bOK2Use = False
bError = True
If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
bError = False
Select Case txtUser.Text
Case "user1"
sSName = "u1sheet"
If txtPass.Text < "u1pass" Then bError = True
Case "user2"
sSName = "u2sheet"
If txtPass.Text < "u2pass" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate

bOK2Use = True
Unload UserForm1
End If
End Sub

Private Sub UserForm_Terminate()
If Not bOK2Use Then
ActiveWorkbook.Close (False)
End If
End Sub

So...what do I need to do to obtain the result I want?

"Tim Williams" wrote:

There's no sheet named with the value of sSName ?

--
Tim Williams
Palo Alto, CA


"EbonLinctus" wrote in message ...
I am using Win XP, Excel 2003.

I receive a "Runtime Error '9': Subscript out of range" message.

Code:

---

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
    Dim bError As Boolean
    Dim sSName As String
    Dim p As DocumentProperty
    Dim bSetIt As Boolean

    bOK2Use = False
    bError = True
    If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
        bError = False
        Select Case txtUser.Text
            Case "user1"
                sSName = "Corp"
                If txtPass.Text < "u1pass" Then bError = True
            Case "user2"
                sSName = "Elect"
                If txtPass.Text < "u2pass" Then bError = True
            Case Else
                bError = True
        End Select
    End If
    If bError Then
        MsgBox "Invalid User Name or Password"
    Else
        'Set document property
        bSetIt = False
        For Each p In ActiveWorkbook.CustomDocumentProperties
            If p.Name = "auth" Then
                p.Value = sSName
                bSetIt = True
                Exit For
            End If
        Next p
        If Not bSetIt Then
            ActiveWorkbook.CustomDocumentProperties.Add _
              Name:="auth", LinkToContent:=False, _
              Type:=msoPropertyTypeString, Value:=sSName
        End If

        Sheets(sSName).Visible = True
        Sheets(sSName).Unprotect (txtPass.Text)
        Sheets(sSName).Activate

        bOK2Use = True
        Unload UserForm1
    End If
End Sub

Private Sub UserForm_Terminate()
    If Not bOK2Use Then
        ActiveWorkbook.Close (False)
    End If
End Sub


---

During the debug it highlights Sheets(sSName).Visible = True

The sheet names are Corp and Elect.

What I am trying to do is have the user enter the Name and password on a
userform to access a specific worksheet.

What is the problem?




--

Dave Peterson

Runtime Error '9'
 

From both responses, I gather that I should substitute the sheet names for
sSName, yes?

All I did was follow the instructions on how to perform this task (see
http://exceltips.vitalnews.com/Pages...y_User.htm l).
My guess is that the instructions was based on the assumption that the
reader would know what to do (I don't).

"Dave Peterson" wrote:

There is no sheet in the activeworkbook named whatever sSName holds.

If you're positive that there is, then look for typing differences--leading or
trailing spaces may be the culprit.



EbonLinctus wrote:

No. As you can tell, I copied the code from Vital News' website.

The original code follows is as follows

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
Dim bError As Boolean
Dim sSName As String
Dim p As DocumentProperty
Dim bSetIt As Boolean

bOK2Use = False
bError = True
If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
bError = False
Select Case txtUser.Text
Case "user1"
sSName = "u1sheet"
If txtPass.Text < "u1pass" Then bError = True
Case "user2"
sSName = "u2sheet"
If txtPass.Text < "u2pass" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate

bOK2Use = True
Unload UserForm1
End If
End Sub

Private Sub UserForm_Terminate()
If Not bOK2Use Then
ActiveWorkbook.Close (False)
End If
End Sub

So...what do I need to do to obtain the result I want?

"Tim Williams" wrote:

There's no sheet named with the value of sSName ?

--
Tim Williams
Palo Alto, CA


"EbonLinctus" wrote in message ...
I am using Win XP, Excel 2003.

I receive a "Runtime Error '9': Subscript out of range" message.

Code:

---

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
    Dim bError As Boolean
    Dim sSName As String
    Dim p As DocumentProperty
    Dim bSetIt As Boolean

    bOK2Use = False
    bError = True
    If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
        bError = False
        Select Case txtUser.Text
            Case "user1"
                sSName = "Corp"
                If txtPass.Text < "u1pass" Then bError = True
            Case "user2"
                sSName = "Elect"
                If txtPass.Text < "u2pass" Then bError = True
            Case Else
                bError = True
        End Select
    End If
    If bError Then
        MsgBox "Invalid User Name or Password"
    Else
        'Set document property
        bSetIt = False
        For Each p In ActiveWorkbook.CustomDocumentProperties
            If p.Name = "auth" Then
                p.Value = sSName
                bSetIt = True
                Exit For
            End If
        Next p
        If Not bSetIt Then
            ActiveWorkbook.CustomDocumentProperties.Add _
              Name:="auth", LinkToContent:=False, _
              Type:=msoPropertyTypeString, Value:=sSName
        End If

        Sheets(sSName).Visible = True
        Sheets(sSName).Unprotect (txtPass.Text)
        Sheets(sSName).Activate

        bOK2Use = True
        Unload UserForm1
    End If
End Sub

Private Sub UserForm_Terminate()
    If Not bOK2Use Then
        ActiveWorkbook.Close (False)
    End If
End Sub


---

During the debug it highlights Sheets(sSName).Visible = True

The sheet names are Corp and Elect.

What I am trying to do is have the user enter the Name and password on a
userform to access a specific worksheet.

What is the problem?




--

Dave Peterson

Runtime Error '9'
 

I wouldn't. I'd find out why sSName didn't contain the string I wanted.

EbonLinctus wrote:

From both responses, I gather that I should substitute the sheet names for
sSName, yes?

All I did was follow the instructions on how to perform this task (see
http://exceltips.vitalnews.com/Pages...y_User.htm l).
My guess is that the instructions was based on the assumption that the
reader would know what to do (I don't).

"Dave Peterson" wrote:

There is no sheet in the activeworkbook named whatever sSName holds.

If you're positive that there is, then look for typing differences--leading or
trailing spaces may be the culprit.



EbonLinctus wrote:

No. As you can tell, I copied the code from Vital News' website.

The original code follows is as follows

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
Dim bError As Boolean
Dim sSName As String
Dim p As DocumentProperty
Dim bSetIt As Boolean

bOK2Use = False
bError = True
If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
bError = False
Select Case txtUser.Text
Case "user1"
sSName = "u1sheet"
If txtPass.Text < "u1pass" Then bError = True
Case "user2"
sSName = "u2sheet"
If txtPass.Text < "u2pass" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate

bOK2Use = True
Unload UserForm1
End If
End Sub

Private Sub UserForm_Terminate()
If Not bOK2Use Then
ActiveWorkbook.Close (False)
End If
End Sub

So...what do I need to do to obtain the result I want?

"Tim Williams" wrote:

There's no sheet named with the value of sSName ?

--
Tim Williams
Palo Alto, CA


"EbonLinctus" wrote in message ...
I am using Win XP, Excel 2003.

I receive a "Runtime Error '9': Subscript out of range" message.

Code:

---

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
    Dim bError As Boolean
    Dim sSName As String
    Dim p As DocumentProperty
    Dim bSetIt As Boolean

    bOK2Use = False
    bError = True
    If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
        bError = False
        Select Case txtUser.Text
            Case "user1"
                sSName = "Corp"
                If txtPass.Text < "u1pass" Then bError = True
            Case "user2"
                sSName = "Elect"
                If txtPass.Text < "u2pass" Then bError = True
            Case Else
                bError = True
        End Select
    End If
    If bError Then
        MsgBox "Invalid User Name or Password"
    Else
        'Set document property
        bSetIt = False
        For Each p In ActiveWorkbook.CustomDocumentProperties
            If p.Name = "auth" Then
                p.Value = sSName
                bSetIt = True
                Exit For
            End If
        Next p
        If Not bSetIt Then
            ActiveWorkbook.CustomDocumentProperties.Add _
              Name:="auth", LinkToContent:=False, _
              Type:=msoPropertyTypeString, Value:=sSName
        End If

        Sheets(sSName).Visible = True
        Sheets(sSName).Unprotect (txtPass.Text)
        Sheets(sSName).Activate

        bOK2Use = True
        Unload UserForm1
    End If
End Sub

Private Sub UserForm_Terminate()
    If Not bOK2Use Then
        ActiveWorkbook.Close (False)
    End If
End Sub


---

During the debug it highlights Sheets(sSName).Visible = True

The sheet names are Corp and Elect.

What I am trying to do is have the user enter the Name and password on a
userform to access a specific worksheet.

What is the problem?




--

Dave Peterson


--

Dave Peterson

Runtime Error '9'
 

But it might be a way to debug the problem.

EbonLinctus wrote:

From both responses, I gather that I should substitute the sheet names for
sSName, yes?

All I did was follow the instructions on how to perform this task (see
http://exceltips.vitalnews.com/Pages...y_User.htm l).
My guess is that the instructions was based on the assumption that the
reader would know what to do (I don't).

"Dave Peterson" wrote:

There is no sheet in the activeworkbook named whatever sSName holds.

If you're positive that there is, then look for typing differences--leading or
trailing spaces may be the culprit.



EbonLinctus wrote:

No. As you can tell, I copied the code from Vital News' website.

The original code follows is as follows

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
Dim bError As Boolean
Dim sSName As String
Dim p As DocumentProperty
Dim bSetIt As Boolean

bOK2Use = False
bError = True
If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
bError = False
Select Case txtUser.Text
Case "user1"
sSName = "u1sheet"
If txtPass.Text < "u1pass" Then bError = True
Case "user2"
sSName = "u2sheet"
If txtPass.Text < "u2pass" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate

bOK2Use = True
Unload UserForm1
End If
End Sub

Private Sub UserForm_Terminate()
If Not bOK2Use Then
ActiveWorkbook.Close (False)
End If
End Sub

So...what do I need to do to obtain the result I want?

"Tim Williams" wrote:

There's no sheet named with the value of sSName ?

--
Tim Williams
Palo Alto, CA


"EbonLinctus" wrote in message ...
I am using Win XP, Excel 2003.

I receive a "Runtime Error '9': Subscript out of range" message.

Code:

---

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
    Dim bError As Boolean
    Dim sSName As String
    Dim p As DocumentProperty
    Dim bSetIt As Boolean

    bOK2Use = False
    bError = True
    If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
        bError = False
        Select Case txtUser.Text
            Case "user1"
                sSName = "Corp"
                If txtPass.Text < "u1pass" Then bError = True
            Case "user2"
                sSName = "Elect"
                If txtPass.Text < "u2pass" Then bError = True
            Case Else
                bError = True
        End Select
    End If
    If bError Then
        MsgBox "Invalid User Name or Password"
    Else
        'Set document property
        bSetIt = False
        For Each p In ActiveWorkbook.CustomDocumentProperties
            If p.Name = "auth" Then
                p.Value = sSName
                bSetIt = True
                Exit For
            End If
        Next p
        If Not bSetIt Then
            ActiveWorkbook.CustomDocumentProperties.Add _
              Name:="auth", LinkToContent:=False, _
              Type:=msoPropertyTypeString, Value:=sSName
        End If

        Sheets(sSName).Visible = True
        Sheets(sSName).Unprotect (txtPass.Text)
        Sheets(sSName).Activate

        bOK2Use = True
        Unload UserForm1
    End If
End Sub

Private Sub UserForm_Terminate()
    If Not bOK2Use Then
        ActiveWorkbook.Close (False)
    End If
End Sub


---

During the debug it highlights Sheets(sSName).Visible = True

The sheet names are Corp and Elect.

What I am trying to do is have the user enter the Name and password on a
userform to access a specific worksheet.

What is the problem?




--

Dave Peterson


--

Dave Peterson