Until we can determine (and fix) why, I’ve penned the below as a ‘fix’ to this issue:


  • Close all Excel Spreadsheets
  • Open the offending CVR and Save As an XLSX spreadsheet (thus removing the ability to store macros in the spreadsheet)
  • Close Excel again
  • Open the now Macroless spreadsheet and Save As an XLSM spreadsheet again (thus enabling us to add Macros)
  • Open any ‘working’ CVR with the aligned code base
  • Navigate to Developer, Visual Basic in the ribbon menus
  • Click on the VBA Project ([Working CVR]) in the list (it will prompt for the password, stored in Lastpass, enter this)
  • Still in the Visual Basic editor, right click the VBA Project([Broken CVR]) and select Insert, Module
  • Copy and paste code from the ‘existing’ donor (working) CVR from / to the following worksheets in the CVR you’re fixing:
    • Module
    • Staff Allocation
    • This Workbook
  • We need to include the ActiveX reference in the project as well so navigate to Tools, References, scroll down until you find Microsoft ActiveX Data Objects 6.1 Library and tick the box associated with this reference
  • Finally, we need to re-add the security to the project so navigate to Tools, VBA Properties and select Protection. Tick the box Lock project for viewing and enter the password (as stored in Lastpass)
  • Click Save, close the project and reopen and see if changing the CVRDate and pressing the Update button, both on the CVR_Collation worksheet, run the appropriate updates with no VBA errors….