- Excel Desktop Client new !
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.
We will capture and disable Excel→Save and safely (or 'cleanly') implement Excel→Close via two new macros.
Here we copy and paste VBA code.
For this article we use an example MortgageCalculator.xls spreadsheet; the steps are common to any spreadsheet.
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
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 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 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.