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.

• This involves four parts,
  1. Distinguish in real time between a spreadsheet 'author' and an Excel Client User of the spreadsheet
     • Set and hide a cell for locked-mode
  2. Add VBA code to disable Excel→Save and destroy or 'shred' spreadsheet just before Excel→Close
     • Capture and modify Excel→Save and Excel→Close
  3. Password protect the spreadsheet VBA
  4. In the Builder modify DATABASE TO EXCEL DESKTOP to turn on locked-mode for an Excel Client-user
     • Activate locked-mode for an Excel Client EASAP

• We need to differentiate between two modes for the spreadsheet,
  1. open-mode → A spreadsheet 'author' is viewing or modifying cells, formulae or macros in the spreadsheet
  2. locked-mode → The User of an Excel Client is modifying input cells and viewing output
     • We add a new sheet to the spreadsheet, we call it EASA
       • Right-click one of the sheet tabs below the grid of cells
       • Select Insert…
       • Select Worksheet
       • Right-click this new sheet's tab
       • Select Rename
       • Type EASA
     • We add a new named range called EASA → empty designates open-mode, non-empty enforces locked-mode
       • On the EASA sheet click A1
       • In the 'Name Box' change A1 to EASA (see screenshot below)
       • Right-click the EASA named range (or cell A1)
       • Select Format Cells…
       • Check both Locked and Hidden
     • We need to password protect the EASA sheet
       • Right-click the EASA sheet tab
       • Select Protect Sheet…
       • For simplicity enter the spreadsheet password (for this article we use my_workbook_password)

Here we copy and paste VBA code.

For this article we use an example MortgageCalculator.xls spreadsheet; the steps are common to any spreadsheet.

• Navigate to the spreadsheet file:
  • MortgageCalculator.xls
• Open the spreadsheet
• Select File→Options→Addins→Customize Ribbon
  • From the list of possible tabs Add Developer (Custom) as a main tab
  • Back in Excel Click Developer tab then Visual Basic
  • Paste the VBA below into the code window at VBAProject (MortgageCalculator.xls) → ThisWorkbook
    • A password protected spreadsheet needs to be unprotected before being deleted
      • → Substitute an existing workbook password for my_workbook_password
  • Ensure that the spreadsheet has events enabled
    • Application.EnableEvents = True must follow any VBA code that disables events

  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.
• Select the Developer→Visual Basic tab
• Right-click VBAProject(MortgageCalculator.xls)
  • Select VBAProject Properties…
    • Select the Protection tab
      • Check Lock project for viewing
        • Enter and confirm a password
        • Close the workbook

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.

• For each DATABASE TO EXCEL DESKTOP with Action:→ 'New' or 'Open'
  • Set,
    • Initialize Named Ranges: EASA (MortgageCalculator.xls) ← Select from dropdown list
    • Initialization Data: LOCKED

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.