0

I have been working on an .xlsb file in Office Excel 2016. I have developed Macros within the VBA project and it has worked fine (up to v25 of the file) with over 3 months of work.

Now I need to make some adjustments to the VBA Macros. When I save and close the file, the VBA code changes are lost when the file is next opened (Windows recognised the correct last saved-date though).

The file does have Macros in the Workbook_Open and Workbook_BeforeClose events, but nothing that has been changed since it worked fine. Of note, there is no Workbook_BeforeSave event in the VBA project.

Troubleshooting steps I have tried:

  1. Removing the Workbook_BeforeClose event in parts and then entirely
  2. Creating a new file from scratch and copying in the VBA code
  3. Saving in both the VBA Editor window and the Excel Application window
  4. Saving as a new filename

Here is the entire ThisWorkbook module, in case it helps:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  On Error Resume Next
  Application.DisplayAlerts = False
  Sheets("Picking").Delete
  Sheets("Ordering").Delete
  Clear_UI
  Dim UI As Worksheet
  Set UI = Sheets("User Interface")
  With UI
    .Shapes("Menu_StartNewEvent").OnAction = ""
    .Shapes("Menu_UpdateEvent").OnAction = ""
    .Shapes("Menu_ProduceSheets").OnAction = ""
    .Shapes("Menu_CountIn").OnAction = ""
    .Shapes("Menu_EditPackages").OnAction = ""
    .Shapes("Menu_EditCocktails").OnAction = ""
    .Shapes("Menu_EditInventory").OnAction = ""
  End With
  Unhide_Them
  Set UI = Sheets("Inventory Editor")
  UI.Shapes("SaveButton").OnAction = ""
  Set UI = Sheets("Cocktail Editor")
  UI.Shapes("SaveButton").OnAction = ""
  Set UI = Sheets("Package Editor")
  UI.Shapes("SaveButton").OnAction = ""
  Hide_Them
  Application.DisplayAlerts = True
  ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
  On Error Resume Next
  Application.DisplayAlerts = False
  ThisWorkbook.Windows.Item(1).Caption = Replace(ThisWorkbook.Windows.Item(1).Caption, " [Repaired]", "")
  ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Name, AccessMode:=xlExclusive
  ThisWorkbook.Saved = True
  Application.DisplayAlerts = True

  Dim UI As Worksheet
  Set UI = Sheets("User Interface")
  With UI
    .Activate
    .Shapes("Menu_StartNewEvent").OnAction = "ThisWorkbook.Start_New_Order"
    .Shapes("Menu_UpdateEvent").OnAction = "ThisWorkbook.Update_Event_Details"
    .Shapes("Menu_ProduceSheets").OnAction = "ThisWorkbook.Build_Only_Picking_Sheet"
    .Shapes("Menu_CountIn").OnAction = "ThisWorkbook.Build_Only_Picking_Sheet"
    .Shapes("Menu_EditPackages").OnAction = "Edit_Packages"
    .Shapes("Menu_EditCocktails").OnAction = "Edit_Cocktails"
    .Shapes("Menu_EditInventory").OnAction = "Edit_Inventory"
  End With
  Clear_UI
  Set UI = Sheets("Inventory Editor")
  UI.Shapes("SaveButton").OnAction = "Save_Inventory_Editor"
  Set UI = Sheets("Cocktail Editor")
  UI.Shapes("SaveButton").OnAction = "Save_Cocktail_Editor"
  Set UI = Sheets("Package Editor")
  UI.Shapes("SaveButton").OnAction = "Save_Package_Editor"
  Hide_Them
End Sub

I have looked at question/answer here: StackOverflow:xla-file-not-saving-in-excel-2010

Sercho
  • 305
  • 2
  • 9
  • Note that because of `On Error Resume Next` you cannot see any errors at all. This line hides **all** errors until `End Sub` but the errors still occur, you just cannot see their messages. Remove that line and see if you get any errors. • Also set `Application.DisplayAlerts = True` otherwise you won't see if some alerts tell you that something went wrong. • What happened if you used `Save As` and save to a new filename after a change? Did the file save at all? – Pᴇʜ Mar 21 '19 at 07:23
  • @Pᴇʜ I tried removing the On Error handling line and ran the code through, with no errors alerted. I also removed the entire BeforeClose event prior to Saving one time. So there isn’t an error in this module. Also, I tried Save As (see troubleshooting steps in OP) but it had the same result just with a new file name. – Sercho Mar 21 '19 at 07:26
  • That's almost impossible. Can you save a macro if you open up a completely new clean file? So can you for sure tie the issue down to that specific file? Or does it happen to other files (in xlsb) too? – Pᴇʜ Mar 21 '19 at 07:31
  • @Pᴇʜ I know!! Hence my frustration. This problem is somehow linked to this file (or the contents). I have other XLSM files that work fine. I was able to copy the VBA modules into a fresh workbook and have them save, but then when I added the remaining data in the file, the same issue presented. – Sercho Mar 21 '19 at 07:32
  • Just to be clear. If you copy all the macros into a new file (without data) you can save it as xlsb. Then you can re-open it and make changes to the macros and save these changes again? Correct? • Can you please name which other events did you use in `ThisWorkbook`? – Pᴇʜ Mar 21 '19 at 07:37
  • @Pᴇʜ that worked to save it the first time. But then when I re-opened the file and made more changes they weren’t saved. – Sercho Mar 21 '19 at 07:40
  • So this is clearly a VBA code issue that prevents you from saving the file. Please [edit] your question and add the code of all the events you used in `ThisWorkbook`. • Alternatively copy one event after another into a new file to see which one is the problematic one that causes this issue. – Pᴇʜ Mar 21 '19 at 07:41
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/190421/discussion-between-sercho-and-p). – Sercho Mar 21 '19 at 07:54

0 Answers0