Lock Down Spreadsheet to Specific Users

[Falko26]

Hey Team,

Curious questions here. My company has just finished developing an Excel Spreadsheet full of VBA code and functions that we wanted to protect.
Is there a way that we can lock down our spreadsheet from an access perspective. Like you have to have some credentials to be able to sign into it? We really would like it to work as a licensing system where if you leave the company we can remove the license from that employees email which would stop them from accessing the document.

Does anyone have any ideas on achieving this goal?

 

[Sufiyan97]

Sure. Here's a project that I have been working on. The admin id and password are in the file title. Here's what I have so far...and I look forward to any suggestions to improve it.

This sheet will require users to log in. It is partially based on concepts from others…then I took it a few steps further.

Features:

• It will default to the username of whoever is logged into the computer.
• Users are allowed 3 attempts to log in before the app closes.
• Users cannot close out of the login form.
• Users and administrators can be defined within the application.
• Users and administrators can change their passwords for the apps.
  • Passwords can be alphanumeric. No other restrictions have been put in place.
• Successful logins are tracked for audit purposes.
• Logged in user is shown in the status bar.
• Administrators define which tabs a user can see. The rest are automatically hidden.
  • This is currently set to allow up to 6 sheets for each user.
• As new tabs are added (or names changed), an administrator can trigger an update to the data validations used for allowing users to see specific tabs. Alternatively, it will update the next time the sheet is opened.
• Users will not be able to unhide sheets on their own. They will only see what the admin allows them to see
• ‘SaveAs’ is only allowed for Admins. Users can only save.
• The VBProject is locked.
• There is a recovery feature where if the admins forget their password, they can still log into the app and get the password for the VB project.
• Upon closing the app, all tabs are hidden to get ready for the next user to open the app and log in.
  • In case the app crashes before tabs are hidden, they will also be hidden upon opening the sheet.

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

1drv.ms

You'll want to change the SuperAdmin password once you log in, as well as the password to open the VBA project. Let me know if you have any questions.

Hi @Candyman8019

What is the password to open the file?

 

[Falko26]

You would need to remove their login details from the 'approved list'.

Ok, but this is a spreadsheet that is used for each project so there are multiple copies of it everywhere. so I wouldn't be able to keep each any every version up to date with the login credentials. One thing CandyMan said was to have the spreadsheet check the device ID or domain name before allowing access. I believe this would work to limit entry of the spreadsheet to only computers owned by the company. I would also like to use a login credential like you stated as an added layer of security.

 

[Candyman8019]

There is no password to open. Once you do open it, you can use the ID and Password indicated in the filename to log in.

I just added a piece to check the domain...you'll need to update the domain name and uncomment a couple of lines in the 'ThisWorkbook' module.

 

[Candyman8019]

Ok, but this is a spreadsheet that is used for each project so there are multiple copies of it everywhere. so I wouldn't be able to keep each any every version up to date with the login credentials. One thing CandyMan said was to have the spreadsheet check the device ID or domain name before allowing access. I believe this would work to limit entry of the spreadsheet to only computers owned by the company. I would also like to use a login credential like you stated as an added layer of security.

You can modify this sheet as you see fit to remove the need for a password...so as long as the user is on your domain, it would open.

 

[Falko26]

There is no password to open. Once you do open it, you can use the ID and Password indicated in the filename to log in.

I just added a piece to check the domain...you'll need to update the domain name and uncomment a couple of lines in the 'ThisWorkbook' module.

Did you re share the document after you added the piece to check the domain?

 

[Candyman8019]

Updated link...

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

1drv.ms

 

[Falko26]

Updated link...

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

1drv.ms

Awesome I appreciate it! This will defiantly get me started down the right path. Ill let you know if I come up with any additions or have additional questions.

 

[Candyman8019]

Right on Falco. From what you've described you'll only need a small fraction of what is in there. Take what you need and modify as you see fit.

 

[Falko26]

Right on Falco. From what you've described you'll only need a small fraction of what is in there. Take what you need and modify as you see fit.

Pretty quick question right off the bat here but. Do you know if instead of domain name you can use device ID instead? Then I can just add everyone's Device ID to the list that should have access. Our Domain is ran on the cloud so I'm having trouble locating what my domain name even is.

 

[Candyman8019]

yes, you can. it would be environ("Computername") I believe...