Workbook_BeforeSave problems with saving

Hi,

I have a problem with some of my code which has baffled me for ages & was hoping someone can help me resolve the issue.

I have a BeforeSave macro that works ok when you save manually, however there are 2 other instances that do not work:

1) Using "ThisWorkbook.Save" in another macro does not save the changes. (when you close the workbook and answer no to saving changes you lose changes that were made before running the "ThisWorkbook.Save")

2) If i have a 2nd workbook open at the same time I get stuck in an eternal loop when asked if i want to save changes when closing the workbook. (just keep getting the dialog box asking if i want to save changes, click yes and it comes back)

I hope I have explained this clearly and really do appreciate any advice that can be given

BeforeSave Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   
Application.EnableEvents = False
Application.ScreenUpdating = False
    
'************************************************************************************************
If SaveAsUI = True Then ' If user tries to SaveAs then cancel
    Cancel = True
    MsgBox "You cannot use 'Save As' with this workbook.  Use 'Save'", vbCritical, "Cannot Save As"
'************************************************************************************************
Else ' Save workbook in opening workbook state
' Cancel original save request
    Cancel = True
' Unprotect worksheets
    Call UnProtect_Worksheets
' Unprotect workbook
    Call UnProtect_Workbook
' Get current worksheet name
    Dim CurrentSheet As String
    CurrentSheet = ActiveSheet.Name
' Select Open Message worksheet & hide all other worksheets
    Dim s As Worksheet
    Const wsKeep As String = "Open Message"
    Worksheets(wsKeep).Visible = True
    Worksheets(wsKeep).Select
    For Each s In Worksheets
        If s.Name <> wsKeep Then s.Visible = xlSheetHidden
    Next s
' Make Enable Macro Instructions worksheet visible
    Sheets("Enable Macro Instructions").Visible = True
' Protect worksheets
    Call Protect_All_Worksheets
' Protect workbook
    Call Protect_Workbook
' Save this workbook
    ThisWorkbook.Save
'************************************************************************************************
' Return to worksheet that was open before save
' Unprotect worksheets
    Call UnProtect_Worksheets
' Unprotect workbook
    Call UnProtect_Workbook
' Select worksheet that was open when save was selected by user
    Sheets("" & CurrentSheet & "").Visible = True
    Worksheets("" & CurrentSheet & "").Select
' Hide Enable Macro Instructions & Open Message worksheets
    Sheets("Enable Macro Instructions").Visible = False
    Sheets("Open Message").Visible = False
' Protect worksheets
    Call Protect_All_Worksheets
' Protect workbook
    Call Protect_Workbook
'************************************************************************************************
End If
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Regards
Steve

I suspect that this may be a problem:

' Save this workbook
    ThisWorkbook.Save
'************************************************************************************************

You are in an event handler, the BeforeSave event, in fact, and are trying to save the workbook. I would expect that to fire the BeforeSave event again.

I would suggest that you try:

' Save this workbook
    Application.EnableEvents = False
    ThisWorkbook.Save
    Application.EnableEvents = True
'************************************************************************************************

No guarantees, but it should help. Try putting some Stops in the events and stepping through to see what happens.


Regards, TMS

Is the other module in PERSONAL.XLSB or another sheet? ThisWorkbook refers to the workbook the macro is writen in.

also get rid of

' Cancel original save request
    Cancel = True

This Is the original save request you are just executing some code before it saves.

Originally Posted by Steve@TRW

Hi Leon,
I had canceled the save at the begining as i wanted to set the workbook back to a certain 'open workbook state' before the save happens, then save after open state is set. The reason I have done this is so if the workbook is opened by a user with there excel set to disable macros they see the page that the before save event has set (It just tells them to turn on macros & gives instructions on how to do that).

But this is a BEFORE SAVE event so all the code in that macro will run before excel saves the cancel = true doesn't stop the save there and then.
when a user clicks save:
Excel "I would like to save",
VBA "ok but first do this" (your Workbook_BeforeSave runs)
Excel "now can I save?",
VBA "cancel = true, so no"

My apologies. I completely glossed over that! I would, however, be inclined to limit switching the event handling off and on to the immediate area that would trigger the event.

The other thing that can happen is that you can switch event handling off and on in an "outer" module and do the same in an "inner" module. Depending what the subsequent code in the outer module does, event handling may have been switched on earlier than you had intended.

I think that, because you set Cancel=True, you are effectively cancelling the save ... hence, when the user says, "no, don't save", that's exactly what happens.

Probably more random ideas than a concrete suggestion ...

Regards, TMS

Hi Leon,

I understand what you are saying however if i remove the cancel command from the before save event, i do not get the required result.

If i leave it in, it works as required when i manually save, just not when I saving calling the ThisWorkBook.Save in a seperate module.


The BeforeSave event is doing the following:

1) Cancel the original save (I do this as I do not want to save all of the BeforeSave event)

2) Take note of current active worksheet

3) Show/select "open worksheet" & hide all others

4) Now save with ThisWorkBook.Save (enable events is set to false so wont recall BeforeSave event again)

5) After saving, return to worksheet that was open before save and hide "open worksheet".

Result is that user has saved workbook in the required open workbook state then after saving is returned to worksheet they were on when they saved so they could if required continue editing workbook. If i did not cancel original save, each time a save was pressed a user would be sent back to the "open worksheet".

Regards
Steve

PS please try not to get annoyed if I am still not understanding what you are saying. I do appretiate your help and do not want to offend/irratate you or anyone else thet is good enough to give up their time to help me.

do any of your Call macros end with enable events = true?

Did you test if BeforeSave event is being raised after ThisWorkbook.Save call?

Originally Posted by Steve@TRW

Just to clarify as mentioned above, save when closing workbook does work with this BeforeSave event its just when another workbook is open it gets stuck in an eternal loop.

Excel like most programs has ways of recognising infinite loops and getting out of them it is possible that it just can't recognise the loop with two books open rather than it only existing with two open.

Originally Posted by Steve@TRW

Hi

Just had a thought that may resolve one of my issues.

Is there a code that i could put in an Open event in this workbook that makes it open in a new instance of excel (rather than on top of any existing open workbooks)?

Regards
Steve

No unfortunately Application has to be open first as the platform for any macro execution and alternate instances cannot interact. You could write code to open another instance of excel but it would not be able to open a workbook in that instance.

however having said that there may be some shell comand you could write that has a shot at this.

It is possible to reopen file in separate instance and close current version. You may try:
Module:

Sub Highlander()
   Dim wb                          As Workbook
   Dim lCounter                    As Long
   If Application.Workbooks.Count > 1 Then
      For Each wb In Application.Workbooks
         If wb.Windows(1).Visible And wb.Path <> "" Then lCounter = lCounter + 1
      Next wb
      If lCounter > 1 Then
         ThisWorkbook.ChangeFileAccess xlReadOnly
         With CreateObject("Excel.application")
            .EnableEvents = False
            .Workbooks.Open ThisWorkbook.FullName
            .Visible = True
            .EnableEvents = True
         End With
         ThisWorkbook.Close False
      End If
   End If
End Sub

ThisWorkbook:

Private Sub Workbook_Open()
   Application.OnTime Now(), "Highlander"
End Sub

You may try:

Sub Highlander()
   Dim wb                          As Workbook
   Dim lCounter                    As Long
   If Application.Workbooks.Count > 1 Then
      For Each wb In Application.Workbooks
         If wb.Windows(1).Visible And wb.Path <> "" Then lCounter = lCounter + 1
      Next wb
      If lCounter > 1 Then
          ThisWorkbook.Saved = True
         ThisWorkbook.ChangeFileAccess xlReadOnly
         With CreateObject("Excel.application")
            .Workbooks.Open ThisWorkbook.FullName
            .Visible = True
         End With
         ThisWorkbook.Close False
      End If
   End If
   ' On opening workbook do...
    Application.ScreenUpdating = False
' Select Home Page worksheet & hide all other worksheets
    Dim s As Worksheet
    Const wsKeep As String = "Home Page"
    Worksheets(wsKeep).Visible = True
    Worksheets(wsKeep).Select
    For Each s In Worksheets
        If s.Name <> wsKeep Then s.Visible = xlSheetHidden
    Next s
    Application.ScreenUpdating = True
End Sub

with simply:

Private Sub Workbook_Open()

 Application.OnTime Now(), "Highlander"
 
End Sub