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

 

[njimack]

This sounds very similar to a spreadsheet I recently designed. Most of the workbook would be accessible to all users, but certain sheets could only be selected by specified users.

Rather than having the user input a password, I based it around the users logon id - Environ ("Username"). I had a sheet called 'Users' which listed the authorised users. These were in a dynamic named range called 'User_ID'.

The protected sheets were Very Hidden, and could only be accessed via a command button which turned their visible state to Visible, as long as their logon id was found on the 'Users' sheet.

Obviously, the 'Users' sheet could only be accessed by authorised users.

The relevant code is as follows:

Dim User As Integer
Dim Username As String

Username = Environ("Username")

On Error GoTo Bad_User
User = Application.WorksheetFunction.Match(Username, Range("User_ID"), 0)
Sheets("Users").Visible = True
.
.
.
Bad_User:
Call MsgBox("You don't have access", vbCritical, "ACCESS DENIED")

 

[Russ At Index]

Hi Neil,

Thanks for the response.

Am a bit confused though ( Its Friday ) !

I intend to have one worksheet within my workbook,
are you indicating that the user sheet would in fact be
shhet 2 ?

Would be grateful if you could elaborate a bit , your solution
does look like the best option.

Regards

Russ

 

[njimack]

Russ,

Yes, that's right. There would be a very hidden sheet which lists the logon ids of the authorised users.

The code I posted compares the current user to the list of authorised users. If no match is found, it displays a message box saying the user doesn't have authorisation, and the macro ends. If a match IS found, the code continues as per normal.

Hope that makes sense.

 

[DominicB]

Good morning Russ At Index

I have a spreadsheet available that allows users to view specified sheets (tabs) where the administrator has granted them rights to do so. The administrator can view all sheets. The workbook does not utilise the flimsy worksheet /workbook protection facility so it is not that easy to get into, but it could be done by an experienced, knowledgable user - Excel is not a secure environment. Fact.

Quite a few others have used this spreadsheet succcessfully, as a shared file or a private file. If you (or anyone interested) want to use the spreadsheet, then send me a mail / PM and I'll forward it on.

HTH

DominicB
dominic@dom-and-lis.co.uk

 

[Russ At Index]

Hi Neil , Dominic.

Thanks for the quick response.

Am in an Ops Meeting this afternoon which will be
aimed at setting up this shared workbook.

Will have more details on Monday !

Would you both be around should i need help?

Dominic , thanks for the offer to PM you , i will more
than likely take up your kind offer.

Have a good weekend Guys & Thanks again

Russ

 

[bsnapool]

DominicB

Is it possible you could send me the spreadsheet?

 

[Russ At Index]

Hi Dominic,

When we have finalised the info / data we want to extract
from the workbook , i will send you a prototype !

Ok to send PM ?

Russ

 

[DominicB]

Hi Russ At Index

Ok to send PM ?

OK!

DominicB

 

[ablazes]

Neil,

I think what you've done here might be very useful to me. Unfortunately, I'm bit of a novice at this. Would you please tell me specifally what sheets I need to create and the columns within so that it's fully functional?

I think I know how to copy and paste the code into Excel.

Thanks.

This sounds very similar to a spreadsheet I recently designed. Most of the workbook would be accessible to all users, but certain sheets could only be selected by specified users.

Rather than having the user input a password, I based it around the users logon id - Environ ("Username"). I had a sheet called 'Users' which listed the authorised users. These were in a dynamic named range called 'User_ID'.

The protected sheets were Very Hidden, and could only be accessed via a command button which turned their visible state to Visible, as long as their logon id was found on the 'Users' sheet.

Obviously, the 'Users' sheet could only be accessed by authorised users.

The relevant code is as follows:

Dim User As Integer
Dim Username As String

Username = Environ("Username")

On Error GoTo Bad_User
User = Application.WorksheetFunction.Match(Username, Range("User_ID"), 0)
Sheets("Users").Visible = True
.
.
.
Bad_User:
Call MsgBox("You don't have access", vbCritical, "ACCESS DENIED")