Making a spreadsheet tamper proof.

[sprucethebruce]

How many options can you remove from a spreadsheet?

I want to lock out as much as possible, ideally I don't want the sheet to be able to be closed without using a password or something similar. If thats not possible, I'd like to lock out as many toolbars and options as possible.

Anyone have any ideas? I've password protected the sheet but if I can even further minimise what can be accessed it'd be great.

 

[oreo haven]

Bruce,
I was having a problem with some folks "accidentally" editing my data, and I found the answer here on MR. Excel. My apologies to the original author.

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If bIsClosing Then Exit Sub
Dim response As Integer
    With ThisWorkbook
        If bMadeChanges Then
        response = MsgBox("Save Changes?", vbYesNoCancel, "SAVE")
        Else
        .Close False
        End If
    
        Select Case response
        
        Case vbYes
        bIsClosing = True
        bMadeChanges = False
        HideAll
        .Close False
        bIsClosing = False
            
        Case vbCancel
        Cancel = True
        
        Case vbNo
        .Saved = True
    
        End Select
    End With
End Sub
Private Sub Workbook_Open()
Call ShowAll
ThisWorkbook.Sheets("WARNING").Activate
MsgBox "THE DATA CONTAINED IN THIS SPREADSHEET IS ONLY AS ACCURATE AS THE DATA ENTERED INTO OUR DATABASE.  IF YOU HAVE ANY QUESTIONS PLEASE CONTACT JONH DOE AT EXT. 123."
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then
    MsgBox "Sorry, you are not allowed to use Save As.", vbCritical, "No Save As"
    Cancel = True
    Exit Sub
    End If
If bIsClosing Then Exit Sub
Call HideSaveShow
Cancel = True
bMadeChanges = False
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
bMadeChanges = True
End Sub

I added a worksheet in by workbook explaining how to turn on macros. The meesage box can also be removed, it was just something I used to cover my own anatomy. After using the above code I never had another problem with someone changing my data.

 

[sprucethebruce]

Where would I paste this code? Into the workbook or just into the code.

I'm not sure if it'll work for my specific purpose as I require cells to be edited and saved, I just don't want them to be able to close the sheet, or fiddle with anything other than they are meant to.

It does however appeal greatly to a number of other spreadsheets I have which I do not want any changes made.

 

[oreo haven]

When you go into view code, use project explorer and put the code into workbook. If you are trying to lock all except certain cells then I am sorry to say that I am too ignorant to help.

 

[sprucethebruce]

Thats what I tried and its coming up with a compile error in the macro.

Shame, I'm using Excel 2003, don't know if this would be the issue?

 

[sprucethebruce]

Its the last line of the macro that isn't working. Any ideas? I just tried it in a new blank workbook so it doesn't clash with anything else I'm running.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
bMadeChanges = True
End Sub

 

[oreo haven]

You know it would have helped if I would have provided all of the code.
Insert a Module and past the following into it: (this code requires you to have on tab labeled WARNING, if you change that in the code you can call it whatever you like. It is in that sheet that I explain how to enable macros. I apologize for not posting the code in it's entirety.

Option Explicit
Public bIsClosing As Boolean
Public bMadeChanges As Boolean
Sub HideSaveShow()
Dim CurSht As Worksheet
    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    Set CurSht = ActiveSheet
    Call HideAll
    Call ShowAll
    CurSht.Activate
    .EnableEvents = True
    .ScreenUpdating = True
    End With
End Sub
Sub HideAll()
Dim sht As Worksheet
    With ThisWorkbook
    .Sheets("WARNING").Visible = xlSheetVisible
 
        For Each sht In .Worksheets
        If sht.Name <> "WARNING" Then sht.Visible = xlSheetVeryHidden
        Next sht
 
    .Save
 
    End With
End Sub
Sub ShowAll()
Dim sht As Worksheet
If bIsClosing Then Exit Sub
    With ThisWorkbook
 
        For Each sht In .Worksheets
        sht.Visible = xlSheetVisible
        Next sht
    .Sheets("WARNING").Visible = xlSheetVeryHidden
 
    End With
 
    bMadeChanges = False
 
End Sub