Shared Workbook - Login & Password To Enable ??

[Russ At Index]

Hi Guys & Gals,

Have searched for several hours but have drawn
a blank on the following shared file - login / password senario !

If this is not possible , is doesn't become a show stopper.It
would just make my boss feel a little happier knowing that all
and sundry could not view this particular workbook.

Here's what i would like to happen , if possible

We have a new shared workbook, when user clicks to open, i
would like a login in screen to appear , then once the user has
entered there username , another screen pops up to request a password.

There will be a limit of 6 users.

So i assume that somewhere i would have to "allocate" user names
and passwords ?

If anyone out there in Excel land has any ideas or thoughts i would
be grateful

Thanks In Advance

Russ

 

[Joe Was]

This is a different way of automatically restricting access.
Sheet1 is the main screen sheet and can be seen by everyone all the other Sheets are hidden. If the UserName of the PC used matches the name of a sheet then that Sheet is automatically made visable as well.

This way people can only see Sheet1 and the Sheet named the same as there UserName!


Public thisUserN$
'Note: the Public Var above should be the
'1st entery at the top of the
'ThisWorkbook module!

Private Sub Workbook_Open()
'ThisWorkbook module code!
'Show User Name!
Dim sh As Worksheet

If Application.UserName = "" Then GoTo NoNam Else GoTo HNam
End

NoNam:
'User name not in system.
'Get User Name as input.
UNText = Application.InputBox( _
prompt:="The user: " & UserName & " has not indicated their name," & Chr(13) & _
"in the Tools-Options-General, User Name: box of Excel." & _
Chr(13) & "Or the network system User Name was not found?" & _
Chr(13) & Chr(13) & _
"Please enter the User Name here:" & Chr(13) & Chr(13) & _
"Note: This will not change the User Name in Options!", _
Title:="User Name not installed on this PC!")

HNam:
thisUserN = Application.UserName

'On Error GoTo myEnd

For Each sh In Worksheets
If sh.Name = thisUserN Then n = n + 1

If (sh.Name = "Sheet1" Or sh.Name = thisUserN) Then
sh.Visible = True
Else
sh.Visible = False
End If
Next sh

If Not n > 0 Then MsgBox "You are not authorized for other sheets!"
If n > 0 Then Sheets(thisUserN).Select

'Option: Exit Application, un-comment below!
'Application.Quit

myEnd:
End Sub

 

[njimack]

Ablazes,

To use this method, you need a very hidden worksheet (the name is irrelevant). On this sheet you have a named range (preferably dynamic) which houses the authorised users ID's.

Then you use the above code within any procedure which can only be carried out by authorised users. If the macro doesn't find the current user within the list of authorised users, it displays the message box and the macro ends.