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?

 

[Logit]

How knowledgeable are the users regarding the inner workings of Excel and VBA ?

 

[Falko26]

The Users are not knowledgeable with Excel VBA. The spreadsheet is built to function like an app. simplicity is key so all Macros are ran with Forms buttons with minimal data entry cells.

 

[Candyman8019]

Excel isn't super secure, however, you can do a few things to thwart most users.

To start, I would have a workbook_open script that hides (xlVeryHidden) all tabs.
It would then check the domain that the user is on, ie they can only open it on a company domain connected device.
Follow that by detecting the userID of the workstation that opens the file. From there, you can have another xlVeryHidden sheet that lists userIDs who are allowed to access the doc. Once those conditions are met, the applicable tabs are made visible to the user.

You'll also want to disable SaveAs functionality and password protect your VBA project. And if there is a concern of someone trying to get around that, I would also recommend 'Encrypt With Password' to open the file. That should stop most people trying to access it without permission.

 

[Logit]

Would you be ok with a UserForm that 'pops up' requesting a user name and password ? If either entry is wrong the entire log in process shuts down ?

 

[Falko26]

Would you be ok with a UserForm that 'pops up' requesting a user name and password ? If either entry is wrong the entire log in process shuts down ?

Yes that would be ok. However, If a person leaves the company will there Username and Pasword still allow them to access the document?

 

[Falko26]

Excel isn't super secure, however, you can do a few things to thwart most users.

To start, I would have a workbook_open script that hides (xlVeryHidden) all tabs.
It would then check the domain that the user is on, ie they can only open it on a company domain connected device.
Follow that by detecting the userID of the workstation that opens the file. From there, you can have another xlVeryHidden sheet that lists userIDs who are allowed to access the doc. Once those conditions are met, the applicable tabs are made visible to the user.

You'll also want to disable SaveAs functionality and password protect your VBA project. And if there is a concern of someone trying to get around that, I would also recommend 'Encrypt With Password' to open the file. That should stop most people trying to access it without permission.

This seems to check all the boxes. I like the idea of checking Domain and Device IDs before allowing the user to see anything on the sheet. That way they need to have a company computer to access the document.
Do you have any info on getting started implementing these security measures?

 

[Logit]

Yes that would be ok. However, If a person leaves the company will there Username and Pasword still allow them to access the document?

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

 

[Candyman8019]

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.

Error

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.

 

[Candyman8019]

The one piece I don't have in there is checking the domain...but that can be added super easily.