Spreadsheets per user (Password Protect)

Amorous

New member
Joined
Aug 19, 2011
Messages
6
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

New member
Joined
Aug 19, 2011
Messages
6
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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,
 

Attachments

  • xlgf355-1.xls
    197.5 KB · Views: 455
Last edited:

jars121

New member
Joined
Feb 5, 2013
Messages
3
Reaction score
0
Points
0
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 :)
 
Last edited:

LuisPacheco

New member
Joined
May 30, 2013
Messages
12
Reaction score
0
Points
0
Excel Version(s)
2016
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

New member
Joined
Feb 5, 2013
Messages
3
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

New member
Joined
May 30, 2013
Messages
12
Reaction score
0
Points
0
Excel Version(s)
2016
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<<<<
View attachment Username with Worksheet.xls
 

LuisPacheco

New member
Joined
May 30, 2013
Messages
12
Reaction score
0
Points
0
Excel Version(s)
2016
Hi Jars thanks for your prompt response, could you please share your file just to check your option? Many Thanks
 

LuisPacheco

New member
Joined
May 30, 2013
Messages
12
Reaction score
0
Points
0
Excel Version(s)
2016
Hi Ken

Sorry for the late response, I just made the changes to the VBA code that RoyUK's provide me and it's work pretty good (There are a few things but I will check with royUk's to verify if it's possible to takes the password and the user from another excel file). I was trying to integrate to my project another VBA codes but I couldn't find anything that could works for me. The first one is: It’s possible to copy an specific range from the actual sheet and pasted in another workbook with the condition "IF the cell is "blank" then paste the data copy from the previews sheet and if the cell is not "blank" then paste de data on the next row. The second one is: Can we save the current excel file in a folder (Hyperlink) that is specify in the cell "A1" using a VBA code? I hope that you can shed a light on this... Thanks again and Have a good weekend =)
 

Kprajath

New member
Joined
Dec 15, 2014
Messages
10
Reaction score
0
Points
0
Hi Ken,
i have seen this post and the solution given by you was very much helpful for me in fine tuning my application. a big THANKS to you Sir. i request your help to tackle a problem i am unable to fix. in my Application, i have created a menus on two separate worksheets with control buttons as options and user can select from it. At present these menu sheets are unlocked depending on the user Ids. i wish to make it in one single sheet with buttons which are restricted for users down the level should be deactivated when they access the application (with their user id entered in the beginning). Requesting you to kindly help me.
 

jakmc25

New member
Joined
Apr 25, 2018
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2016
Do you have this same file set up as a excel 2016 version. I tried saving this one and changing to a .XLSM or .XLSX and it wouldn't let me.

Sorry I know this post is very old!

Thanks for the help.
 

jakmc25

New member
Joined
Apr 25, 2018
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2016
The First file that ken posted on this thread.
 
Top