ExcelBanter - View Single Post

Dave Peterson

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