User Tools

Site Tools

Implement locked-mode for an Excel Client spreadsheet

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

A. Set and hide a cell for locked-mode

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

B. Capture and modify Excel→Save and Excel→Close

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.

  • 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

C. Password protect the spreadsheet VBA

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

D. Activate locked-mode for an Excel Client EASAP

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