Genoseeker, Peter,
If you check out Peter's link you will see that it utilises input boxes to receive usernames and passwords before making a specific sheet visible to each valid user.
I offer below an alternative that allows an administrator to control and vary, which of the sheets are to be visible to each user. Password entry is made by way of a textbox on a user form. This type of textbox allows for the disguise of password characters by using *****.
I believe that this soution provides pretty reasonable security given that Excel and vba can never be considered truly secure.
I have included only password entry, rather than username and password, but that could easily be added.
It might be that if neither this nor Peter's solution, give you exactly what you require, the answer is some combination of the two.
You will require a sheet named Login. Not critical but make it the leftmost of your sheet tabs.
Typically, set the sheet out as below....
Login
| A | B | C | D | E | F | G |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
| Tom | PW1 | | | | | | |
| **** | PW2 | | | | | | |
| Mary | PW3 | | | | | | |
| | | | | | | |
<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 129px"><col style="WIDTH: 91px"><col style="WIDTH: 91px"><col style="WIDTH: 71px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="colspan: 7, align: left"]Enter ALL worksheet names below, C3 to ?3, without leaving a blank in the range.[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="colspan: 7, align: left"]Enter "Y" in appropriate cells to allow user access.[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #c0c0c0"]User[/TD]
[TD="bgcolor: #c0c0c0"]Password[/TD]
[TD="bgcolor: #c0c0c0"]Login[/TD]
[TD="bgcolor: #c0c0c0"]Sheet1[/TD]
[TD="bgcolor: #c0c0c0"]Sheet2[/TD]
[TD="bgcolor: #c0c0c0"]Sheet3[/TD]
[TD="bgcolor: #c0c0c0"]Sheet4[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #ffff99"]Administrator[/TD]
[TD="bgcolor: #ffff99"]Admin[/TD]
[TD="bgcolor: #ffff99, align: center"]Y[/TD]
[TD="bgcolor: #ffff99, align: center"]Y[/TD]
[TD="bgcolor: #ffff99, align: center"]Y[/TD]
[TD="bgcolor: #ffff99, align: center"]Y[/TD]
[TD="bgcolor: #ffff99, align: center"]Y[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="align: center"]Y[/TD]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="align: center"]Y[/TD]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
</tbody>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
** Administrator must be Row 4 and is the only user that will have access to Login sheeet
You will then need to go to the VBE and insert a new Form module.
Add a textbox
Add 2 labels for the instructional texts. One above and one below the textbox.
Add 2 control buttons at the bottom of the form.
Using the Proerties pane, edit /set the following properties for the form objects.
Form - Rename as frmLogin
Label1 - Edit caption to read 'Please enter your password'
Label2 - Edit caption to read 'Passwords are case sensitive'
Label1 & Label2 - Font - Size to suit, say 10
Textbox1 - Rename as tbPWord
PasswordChar enter *
Font - set to suit
CommandButton1 - Rename as cbCancel
Edit Caption to 'Cancel'
CommandButton2 - Rename as cbEnter
Edit Caption to 'Enter'
Change the Default property to True
That last setting Default = True will allow the user to hit Enter key and still trigger the cbEnter button code.
*****
As and when I can figure out how to do it I will upload an image of the form!!!! Any clues will be gratefully received.
*****
You will then need to add the following code.
ALL OF WHICH CAN BE COPIED AND PASTED
In the ThisWorkbook module....
Code:
Private Sub Workbook_Open()
On Error GoTo Out
Sheets("Login").Visible = xlSheetVisible
For Each sht In Sheets
If Not sht.Name = "Login" Then sht.Visible = xlVeryHidden
Next sht
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.WindowState = xlMinimized
Application.EnableCancelKey = xlDisabled
frmLogin.Show
Out:
On Error GoTo 0
End Sub
In the frmLogin module.......
Code:
'Note: Declare this variable in the general area at the top of the module
Public Tries As Integer
Private Sub cbCancel_Click()
Unload Me
ThisWorkbook.Close SaveChanges:=False
End Sub
Private Sub cbEnter_Click()
Application.EnableCancelKey = xlErrorHandler
pW = Me.tbPWord.Value
On Error GoTo BadPW
With Sheets("Login")
pwRow = Application.WorksheetFunction.Match(pW, .Range("B4:B10000"), 0) + 3
On Error GoTo Out
GoTo GoodPW
BadPW:
On Error GoTo Out
Tries = Tries - 1
If Tries = 0 Then
Tries = 3
MsgBox "Access Denied"
Unload Me
GoTo Out
Beep
End If
MsgBox "You have " & Tries & " more attempts to enter a valid password"
Exit Sub
GoTo Out
GoodPW:
On Error Resume Next
Tries = 3
NumCol = Sheets.Count + 2
VisShts = 0
For c = 3 To NumCol
myShtName = .Cells(3, c).Value
If .Cells(pwRow, c).Value = "Y" Then
Sheets(myShtName).Visible = xlSheetVisible
VisShts = VisShts + 1
End If
Next c
Unload Me
If Not VisShts > 0 Then GoTo Out
Sheets("Login").Visible = xlVeryHidden
ActiveWindow.WindowState = xlMaximized
ActiveWindow.DisplayWorkbookTabs = True
If pwRow = 4 Then
Ans = MsgBox("Do you wish access the Login sheet?", vbYesNo)
If Ans = vbYes Then Sheets("Login").Visible = xlSheetVisible
End If
On Error GoTo 0
Exit Sub
Out:
On Error GoTo 0
End With
Unload Me
ThisWorkbook.Close SaveChanges:=False
End Sub
Private Sub UserForm_Activate()
Tries = 3
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Prevents user from closing form with X
If CloseMode = vbaFormControlMenu Then
Cancel = True
MsgBox "Please use the Cancel button!"
End If
End Sub
Unless I have overlooked anything, that should be good to go.
Obviously, you will need to make sure that each use'rs instance of Excel has the appropriate sttings for Macro security.
The only thing that I would add to this is perhaps password protecting the VB Editor so that security cannot be easily compromised via the vba route.
I do hope that that helps.
Please let me know if it does or if you find any errors or omissions.
Tony
