Locking down an excel sheet.

[MasterBash]

Hello everyone,

I am trying to build an excel form that visitors will be filling out. The data on that form will be sent to the Visitor data sheet on Submit. Old data will be sent to Archive and the Information sheet simply gives information about e-mails, data validation, support link, etc...

The registration form will be filled by people we do not know. We want to make sure that they won't have access to other sheets, vba (even if the sheet contains vba, we just don't want them to mess with the code or anything). We don't want them to be able to change anything. All they should be allowed to do is to fill out this form and click submit/clear. However, the team needs access to the other sheets.

We have not decided how we will be doing this, but there are 2 different ways I can think of :
A windows tablet, where this sheet will run the Excel app and the rest of us will be running the web version of the app.

A laptop with a tablet connected to it acting as a second screen.

How would we go about securing our data and to make sure that people are unable to break the form or anything ?

Thank you.

 

[mumps]

First of all, any protection that Excel offers is not particularly strong. Anyone who is persistent enough, could probably do enough research to figure out how to get around the protection. Having said that, There are a few approaches that you could use such as protecting the sheets so they can't be modified and also protecting the VBA Project with passwords. The team could have access to the other sheets using the passwords. You could also use a user form instead of a worksheet to enter the data. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Old data will be sent to Archive

Also, please explain in detail how you want this to be done.

 

[MasterBash]

Dropbox

www.dropbox.com

This project is like 80% done. How it works is, visitors will enter information in the Registration form (* are required). Clicking the cell for the signature pops up a User form where the visitor can draw a signature, then they can clear, use signature or cancel. If they click "Use Signature", there is a code only available in more recent versions is the PlacePictureInCell, which places the signature inside of C19. If that option is not available, the only option out of the signature validation is to comment out

If IsEmpty(Range("C19").Value) = True Then Err.Raise vbError, , "Please enter your signature."

The signature also gets saved to the drive.

When clicking Submit, the registration form will send the data to "Visitor data" sheet. Data that are older than 2 days will move to the Archive sheet. This is already done and working, I would just need to find a way to automate it on a daily basis. The submit button also sends an e-mail to a list of people. The information sheets is being refered to for different information (e-mail address, subject, folder path, additional validation).

The big challenge right now is to lock down the Registration sheet, as that is what visitors will be filling out. I am not exactly sure how to do that.

Thank you.

 

[mumps]

In your original post, you said:

We want to make sure that they won't have access to other sheets

In your last post, you said:

The big challenge right now is to lock down the Registration sheet

Please clarify. Do you want to do both? If both, please explain what you mean by "lock down".

 

[MasterBash]

Sorry, I meant to say that visitors should not have access to other sheets (password protected is good, maybe hidden too to a specific user name), but also they should not have access to toolbars options, etc to mess with the registration sheet. Finding a way so they are unable to close the excel app (no X, no alt f4). Maybe a true full screen?

Thank you.

 

[mumps]

To be honest, you have far too much code already in use for me to describe in any detail what might work for you so I can give you some general suggestions that might help. You can password protect the other sheets to prevent anyone from making any changes to them. Then you would have to add code to all macros that access these sheets to unprotect the sheet at the beginning, run the code and then protect the sheet at the end as in the example below:

Sub Send_The_Emails()
    Sheets("Visitor data").Unprotect "YourPasswordHere"
    'your code here
    Sheets("Visitor data").Protect "YourPasswordHere"
End Sub

This way the sheet will allow the code to perform its actions and always keep the sheet protected. With regards to the registration sheet, you could unlock the cells in column C that require data input and then protect it with a password. This will allow data entry in only the unlocked cells and prevent any changes to the rest of the sheet. To prevent visitors from accessing the code where they would be able to see the passwords, you have to protect your VBA Project.
Do the following:
-hold down the ALT key and press the F11 key to open the Visual Basic Editor
-click on 'Tools' on the top menu
-click 'VBAProject Properties'
-click the 'Protection' tab
-click the box to the left of 'Lock project for viewing' to put a check mark in it
-enter your password and then confirm it and click 'OK'
-close the VB Editor
-save your workbook as a macro-enabled file and close it
When you re-open the file, you will not be able to see the macros unless you enter the password. Keep in mind that this type of protection is not very strong and anyone who really wants to get at your macros can probably do it with a little research. I hope this helps.

 

[MasterBash]

Thank you so much. This certainly helps and it also gives me ideas. I will be messing around with it as soon as possible.
I believe "VeryHidden" could also be an option ? Run a macro to unhide/hide.

I am not sure what to do about the UI though. I would rather them not see the toolbars and stuff, in case they can mess stuff up by changing formatting or something else. However, if we had a graphical tablet that is plugged into a computer and we were to hide the UI stuff, I wonder if it would be possible for the computer's main monitor to have access to everything, while just hiding stuff on the second monitor. I know Excel has a "New window" option in "View".

Also, would it be possible to limit the number of rows / columns someone can scroll ? I know the VBA properties option resets everytime we re-open the file. I wonder if there is a permanent option ? In case a visitor decides to scroll 60000 lines then another visitor that doesn't know much about computers cant find the registration form because he doesn't know how to scroll back up or something :P

 

[mumps]

I don’t know how you would use a second monitor. You can use Ctrl + Home to quickly scroll to the top and Ctrl + End to quickly scroll to the bottom.

 

[MasterBash]

Right now, it has not been decided as to how we will proceed. I will have a meeting about this with the team in a week or two.

If we use the computer + graphical tablet option : The graphical tablet option will have the "Registration form" opened at all times, for the visitors to enter their information. The computer's main monitor will be used to enter the Time of Departure in the Visitor data sheet. We can also open the online version on the main monitor and the app version on the tablet. This is the more expensive option, but it will allow us to verify the visitor's data quickly.

If we simply go to the Windows tablet option : The tablet will run the Registration form and each of us (the team) will have the excel online version opened and simply add the Date of departure in the visitor sheet.

As for ctrl-home and ctrl-end, visitors may not know about those shortcuts. We are trying to make the process as simple as possible for them.

 

[MasterBash]

Small update : This works great so far. I added a way that macros can only be run with specific usernames. I chose to hide all UI stuff and run it full screen.
However, I would need to disable the Esc key. It is easy to exit full screen pressing Esc. I would also like to make Excel not close if someone presses alt-f4 (I should probably disable combo keys too ?) or the X.

I am using VeryHidden for all the sheets except for the Registration form. So far so good. It may not be perfect, I think it might quite secure. I will be testing it out quite a bit to see if it works as it should.