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]

Sounds good.

 

[RoryA]

Seems to me like Microsoft Forms would have been a simpler implementation for a guest entry form.

 

[MasterBash]

Thank you. I heard about the Google forms but after trying it out, we chose not to use it. I never heard of Microsoft forms, but I will test it out this week.

 

[MasterBash]

Sorry, it took a while to reply to this, as we did test things out and we tried Microsoft forms and google forms but we could not (easily) get a signature on those forms, so we ended up using :

Private Sub Workbook_Open()
'I use this for every macro, so they can't be run using alt-f8 :
If Not Environ("username") = "usernamehere" Then
MsgBox "You are not authorized to do this."
Exit Sub
End If

If ActiveSheet.Name = "Registration form" Then

    Application.OnKey "{ESC}", ""
Else
    Application.OnKey "{ESC}"
End If

ThisWorkbook.Unprotect "passwordhere"
Worksheets("Registration form").Select
'This one is actually good to write data with vba to locked cell :
Worksheets("Registration form").Protect UserInterfaceOnly:=True
Worksheets("Registration form").ScrollArea = "A1:C23"
Application.DisplayFullScreen = True
Application.DisplayFormulaBar = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
Worksheets("Visitor data").Visible = xlVeryHidden
Worksheets("Information").Visible = xlVeryHidden
Worksheets("Archive").Visible = xlVeryHidden
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayVerticalScrollBar = False
ThisWorkbook.Protect "passwordhere"
Conditions.Show
End Sub

This one so they can't easily close excel :

Sub CloseMacro()
   Ok2Close = True
   ThisWorkbook.Close SaveChanges:=True
        Application.Quit
    Ok2Close = False
End Sub

No problem so far. We tested it for a a bit and it can become terribly annoying with all the cell validations and stuff, as it should be. Maybe it is not foolproof, as we left some options such as resizing and minimizing, in case we need to fix some stuff on the tablet.

One thing I would like to know :

If Not Environ("username") = "usernamehere" Then
MsgBox "You are not authorized to do this."
Exit Sub
End If

Is it possible to make it into a range of cells in a sheet, so we can easily add/remove usernames ?