Spreadsheets per user (Password Protect)

[Amorous]

Hello. I have an Excel Spreadsheet for employees to enter their Bonuses. The sheet gets printed out and filed. I want each employee to open up their own spreadsheet and make it password protected per user. Do you know of any software or another method on how to accomplish this?

[Ken Puls]

Hi there, and welcome to the forum!

If I understand this correctly, you're trying to make sure that everyone's info is in the same file, but you want each employee to provide a password when they open the workbook that only allows them to see their own sheet?

This could be done with VBA, yes. There's a BIG caveat to this though... If you users in your organization who are skilled with VBA, they could easily break any protection that VBA could offer.

The better solution might be to actually create separate files for each user, then have a macro to consolidate them into a global report for your own purposes.

[Amorous]

Im not familiar with VBA..? The users aren't computer savy so imjust not worried about then looking at each others bonuses. For option two, This is an excel spreadsheet. How would they open a new blank form if I separate them individually?

[Ken Puls]

If you separated them individually, you'd send a separate Excel workbook to each user, with a defined worksheet structure for them to work in. Then you'd get them to send it back to you once complete, save it to a directory and from a master workbook you'd run a macro (VBA) to consolidate them. That's the most secure way that you can do it in Excel, and this is the ideal method if you are going to be emailing the file to your users to complete and then have it emailed back to you.

If you're not worried about your user's hackign your workbook, and you just want it on the network drive that everyone can acces, you could always go with the VBA (macro) route. You'd keep all the data in one workbook (probably different worksheets for each user?) You set up a worksheet that listed a username, password, and the name of the worksheet they could access. After storing the file on a network drive, you'd need a macro so that when any user opened the file they were prompted for their user ID and password, and that would then take them to their sheet.

The latter is probably the easier to set up, but it really depends on how the data will be gathered and your comfort with security vs your user skilset.

[Ken Puls]

Just for fun, I decided to build a workbook that would work to allow central processing of this. I've attached a copy here, and basically here's how it works:

• Macros need to be enabled for it to work
• When you open it, you'll be prompted for a username and password. Start with "Admin" and "admin" respecitvely
• You'll see that it takes you to a table of user ID's and passwords
• Now close and reopen the workbook and use "John Doe" and "123". You should be taken to his page

(I stole the template from your other thread on the email route)

It's not a worry if you would rather pursue the other route, I'm cool with that. As I say I did this for fun. If you did want to pursue this one, we'd still need to add the ability to copy a template when a new user is added.

Based on the pay sheet that you have here, I'd see that you'd end up with one file for each pay period here if you went this way.

Cheers,

[jars121]

Hi Ken, thanks for the above script, it's been very helpful in my latest project. I have an issue though. If I have multiple users accessing the file at once, is there a provision for this? Also, when a user wants to save their worksheet, how can I set it up such that the file only saves their worksheet, and not the hidden worksheets of the other users? If I'm being picky, I'd like to have a button on each users' page that when clicked, saves their page (only) and also saves the Admin page, as this is where the results of each users' input is collated. Any advice on this would be greatly appreciated

[LuisPacheco]

Hi Ken,

I'm new on this type of forum actually I'm subscribing as user just because I'm interesting on the file that you did just for fun =) ... I'm not a guru for VBA code and looking into this file I think that these could help me with the issue that I have. The problem is that I need that user could see a several Worksheet (not just one ) when he open the file or the other option is when the user open the file ask for the sheet that he wants to see. Do you think that this could be possible? Thanks for your support and your prompt response ---Luis Pacheco

[jars121]

Hi! As you can see, I also used this code to build a pretty comprehensive system at my work. I implemented a similar process to what you're after; when a user logs in, multiple pages become visible.

I don't have my document with me now, but basically, you add some code to the VBA which says if username = X, then the following worksheets are visible. You can do that for each user, so that what the user sees when they log in is customisable.

[Ken Puls]

Hi Ken,

I'm new on this type of forum actually I'm subscribing as user just because I'm interesting on the file that you did just for fun =) ... I'm not a guru for VBA code and looking into this file I think that these could help me with the issue that I have. The problem is that I need that user could see a several Worksheet (not just one ) when he open the file or the other option is when the user open the file ask for the sheet that he wants to see. Do you think that this could be possible? Thanks for your support and your prompt response ---Luis Pacheco

Sure, we can do that. Question is, what would you like to see in an ideal situation? We might as well build you what you need, rather than customize something to "sort of" fit what you need.

[LuisPacheco]

Hi Ken,

Thanks for your prompt response, your file could works for me, the only that I need is to have the option that the username could see the specific Worksheet1, Worksheet2, Worksheet3 & Worksheet4. The worksheet1 every time would have an specifc Worksheet name but the other could not and they should not be an issue, I don't know if this is simple but I appreciate your help.

Attached is your file with the worksheet added... The password are the same >>>>Username: Admin Password: admin<<<<
Username with Worksheet.xls