Guide Section: Excel Desktop Client
Overview | Get started with EDC | Simple EDC Example | EDC reference

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.

• Via two new macros we will,
  • capture and disable Excel→Save
  • safely (or 'cleanly') implement Excel→Close

• This involves four parts A-D,
  • A. Set and hide a cell for locked-mode
    • distinguish in real time between an Author-user and an Excel Client-user of the spreadsheet
  • B. Capture and modify Excel→Save and Excel→Close
    • add VBA code to disable Excel→Save and destroy or 'shred' spreadsheet just before Excel→Close
  • C. Password protect the spreadsheet VBA
  • D. Activate locked-mode for an Excel Client EASAP
    • in the Builder modify DATABASE TO EXCEL DESKTOP to turn on locked-mode for an Excel Client-user

• We need to differentiate between two modes for the spreadsheet,
  1. open-mode → an Author is viewing or modifying cells, formulae or macros in the spreadsheet
  2. locked-mode → a User of an Excel Client is modifying input cells and viewing output

1 → We add a new sheet to the spreadsheet, 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

2 → 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

3 → 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)

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,

• MortgageCalculator.xls

2 → Here we copy and paste VBA code.

• 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

3 → 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 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.

• 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.

Overview | Get started with EDC | Simple EDC Example | EDC reference