User Tools

Site Tools

Implement locked-mode for an Excel Client spreadsheet

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.

1. Set and hide a cell for locked-mode

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

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

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

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

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

Page Tools