We need to prevent the User of an Excel Client spreadsheet from viewing, modifying or downloading potentially sensitive or proprietary formulas, macros or data within a spreadsheet.
1 → We add a new sheet to the spreadsheet, call it EASA
2 → We add a new named range called EASA → empty designates open-mode, non-empty enforces locked-mode
3 → We need to password protect the EASA sheet
For this article we use an example MortgageCalculator.xls spreadsheet; the steps are common to any spreadsheet.
1 → Navigate to and then open the spreadsheet file,
2 → Here we copy and paste VBA code.
3 → Ensure that the spreadsheet has events enabled
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim ret As Boolean If Sheets("EASA").Range("EASA") <> "" Then ret = Application.Run("Easa.xlam!EasaRibbon.ProtectSave") Cancel = ret End If End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If Sheets("EASA").Range("EASA") <> "" Then ThisWorkbook.Unprotect "my_workbook_password" Application.Run "Easa.xlam!EasaRibbon.ProtectClean" End If End Sub
If not already protected, let's password protect the spreadsheet so a User cannot remove the macros above.
The final task will activate locked-mode for an Excel Client session by changing the empty EASA named range on the EASA sheet to a string which will turn on the two VBA macros we added in step B.2
Each time a DATABASE TO EXCEL DESKTOP (Action: 'New' or 'Open') is triggered we want the temporary Excel Client spreadsheet to initialize the EASA named range to 'locked' (or any other non-empty string).
The specific details for MortgageCalculator.xls follow, but is the same in general for any Excel Client spreadsheet.
Once the EASA named range is not empty (eg. has value →locked) the Excel→Save event will be disabled and Excel→Close event will clean away the spreadsheet data, formulas and macros before Excel exits.