I am trying to find a way to restrict which sheets a user can view by using a username and password. I am testing it first on a simple workbook called TEST with only 3 sheets and 2 users but eventually it will be used on a workbook with approx. 20 sheets and about 8 users and an administrator.
I am a beginner beginner at VBA.
Here is the code I have so far:
Dim bOK2Use As BooleanPrivate 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 IfEnd SubPrivate Sub UserForm_Terminate() If Not bOK2Use Then ActiveWorkbook.Close (False) End IfEnd SubThe code won't run and asks me to debug. In VBA the yellow arrow points to the following line:
If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 ThenAny help would be greatly appreciated.Thanks in advance!
</pre></pre>
I am a beginner beginner at VBA.
Here is the code I have so far:
Dim bOK2Use As BooleanPrivate 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 IfEnd SubPrivate Sub UserForm_Terminate() If Not bOK2Use Then ActiveWorkbook.Close (False) End IfEnd SubThe code won't run and asks me to debug. In VBA the yellow arrow points to the following line:
If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 ThenAny help would be greatly appreciated.Thanks in advance!
</pre></pre>
