Runtime Error '9'

E

EbonLinctus

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?
 
E

EbonLinctus

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 said:
There's no sheet named with the value of sSName ?

--
Tim Williams
Palo Alto, CA


EbonLinctus said:
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?
Click to expand...
Click to expand...
 
D

Dave Peterson

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.


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 said:
There's no sheet named with the value of sSName ?

--
Tim Williams
Palo Alto, CA


EbonLinctus said:
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?
Click to expand...
Click to expand...
Click to expand...
 
E

EbonLinctus

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/T0087_Protecting_Individual_Worksheets_by_User.html).
My guess is that the instructions was based on the assumption that the
reader would know what to do (I don't).

Dave Peterson said:
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.


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 said:
There's no sheet named with the value of sSName ?

--
Tim Williams
Palo Alto, CA


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?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I wouldn't. I'd find out why sSName didn't contain the string I wanted.
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/T0087_Protecting_Individual_Worksheets_by_User.html).
My guess is that the instructions was based on the assumption that the
reader would know what to do (I don't).

Dave Peterson said:
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.


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?

:

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

--
Tim Williams
Palo Alto, CA


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?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

But it might be a way to debug the problem.
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/T0087_Protecting_Individual_Worksheets_by_User.html).
My guess is that the instructions was based on the assumption that the
reader would know what to do (I don't).

Dave Peterson said:
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.


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?

:

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

--
Tim Williams
Palo Alto, CA


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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime Error '1004' 7
Excel 2003 Worksheets 2
Value from one sheet paste under same value on other sheet. 7
Word macro that can call cells out of Excel for a find and replace 0
Making a macro only work on certain files 2
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Macro to Select text from a point given to a specific word 2
VBA word change keys 0

Top