My suggestion was a macro like this:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.Protect("1234")
End Sub
However, this would not work if users opened with macros disabled.
Therefore this could be combined with using xlVeryHidden for all except one (blank) sheet - which you would need to add. This would hide all sheets in a way that could only be unhidden using a macro, which prevents users from unhiding if they have opened with macros disabled. A macro would also be ran when the sheet was opened (with macros enabled) that unhid the sheets with your data, and hid the blank sheet. Macro codes are below. I've called the blank sheet "Blank" and data sheets "Sheet1" and "Sheet2". Change sheet names as appropriate, and add/remove lines if you have more/less than two data sheets:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.Unprotect ("1234")
Worksheets("Blank").Visible = True
Worksheets("Sheet1").Visible = xlVeryHidden
Worksheets("Sheet2").Visible = xlVeryHidden
ActiveWorkbook.Protect ("1234")
End Sub
Private Sub Workbook_Open()
ActiveWorkbook.Unprotect ("1234")
Worksheets("Blank").Visible = xlVeryHidden
Worksheets("Sheet1").Visible = True
Worksheets("Sheet2").Visible = True
ActiveWorkbook.Protect ("1234")
End Sub
This would mean that when the user saved it, they would no longer be able to see the data sheets if they wanted to continue working. They would either have to close and re-open it, or you could put the following macro in the section for the Blank sheet (not the This Workbook one), which would run the macro to unhide the data sheets if the user clicks to any other cell on the blank sheet:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveWorkbook.Unprotect ("1234")
Worksheets("Blank").Visible = xlVeryHidden
Worksheets("Sheet1").Visible = True
Worksheets("Sheet2").Visible = True
ActiveWorkbook.Protect ("1234")
End Sub
To prevent users changing the macros, you can protect the macros by right-clicking on VBA Project(workbook name), probably near the top left, select VBAProject Properties, then Protection Tab. Check Lock project for viewing, and insert a different password that only you know, then click OK. Others, even if they open your spreadsheet, won't be able to view/change the macros.
As suggested by mole999, workbooks with macros should now be saved as .xlsm, rather than the older .xls format.