BeforeSave Event Loop 1

[Rofeu]

Hello,

I've this beforesave event that basically blocks the file from use in case someone opens it with macro's disabled.

after saving it returns the file to the state it was in when the user hit the save button.

This works fine, but if you close the file through the X in the top right corner of the window, Excel launches the 'Do you wish to save your changes' dialog. If you click 'yes', the event fires, but at the end of that Excel again asks if you want to save your changes. This keeps happening until you say NO. I don't understand this behavior as the last line of code in the event is 'ThisWorkbook.Saved = TRUE'.

I've tried numerous ways of getting it to close after saying yes, but so far nothing has done what I intend.

Can anyone help me out with some suggestions?


Cheers,

Roel

 

[PHV]

Have you tried to play with the EnableEvents property in your code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

PHV hit it, you must surround your code (at least those lines which fire the event) as such ...

Application.enableevents = False

'.. your save code here

Application.enableevents = True

Remember, the Enableevents property (when set to false) will ignore the VBA events only. The same principle applies to worksheet change events or calculate events, etc.

HTH

-----------
Regards,
Zack Barresse

 

[Rofeu]

Hi,

yes, I have that. The problem is that it needs to be set back to TRUE at the end of the code in the beforesave event, which is directly before I get the message again.

Cheers,

Roel

Cheers,

Roel

 

[Rofeu]

Hello,

I found it. Instead of using EnableEvents, I now use DisplayAlerts.

As Follows:

Option Explicit
Dim blnClosing As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    blnClosing = True
End Sub

Private Sub Workbook_Deactivate()
    Application.DisplayAlerts = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strSaveAsName As String
Cancel = True

'Add Your Code Before Saving Here

Application.EnableEvents = False

If SaveAsUI = True Then
    strSaveAsName = Application.GetSaveAsFilename( _
        FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    
    If Not strSaveAsName = "False" Then _
        ThisWorkbook.SaveAs strSaveAsName Else blnClosing = False
Else
    ThisWorkbook.Save
End If

Application.EnableEvents = True

If blnClosing = True Then
    Application.DisplayAlerts = False
    Exit Sub
Else
    
'Add Your Code After Saving Here

End If

If Not strSaveAsName = "False" Then ThisWorkbook.Saved = True

End Sub

Cheers,

Roel

 

[firefytr]

DisplayAlerts are only for alerts and not for any VBA events. Read up on the Help files to make sure you understand the difference - there is a big difference. Your save routine will STILL FIRE with DisplayAlerts set to false, you just won't see it and the alert will take whatever it's default action is. Be careful what you use, ensure it is what you actually need.

-----------
Regards,
Zack Barresse

 

[PHV]

I found it. Instead of using EnableEvents, I now use DisplayAlerts
Really ?

Application.EnableEvents = False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rofeu]

Ok, I see your point with the DisplayAlerts. Funny enough the code above did work for me in an empty test-workbook, but after integrating it into my existing code it didn't anymore.

Still EnableEvents is not a solution.

1. The workbook is closed
2. The BeforeClose event fires
3. The Pop-Up 'Do you wish to save you changes?' shows.
4. on 'Yes' the BeforeSave event fires

That's were my problem is. After the BeforeSave finishes it goes back to step 3 and it keeps doing that unless you click No or Cancel, but it should just continue with closing the workbook after the BeforeSave event is finished.

I thought to suppress the message by setting DisplayAlerts = False and in effect let Excel use the default, but it doesn't get suppressed

I do use EnableEvents within the BeforeSave event, but that setting has no effect on this pop-up as I need to set it back to TRUE before the beforesave event ends and the message pops up afterwards.

Maybe I'm missing something obvious, but I'm not getting there.

Thanks for helping on this. I hope you have another suggestion.


Cheers,

Roel

 

[PHV]

Replace this:
If Not strSaveAsName = "False"
with this:
If strSaveAsName <> False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rofeu]

Hi,

that results in runtime error 5: Invalid Procedure Call or Argument. Won't make a difference though as both cases are essentially the same.

I'm stepping through the code and it does everything I want it to do. This pop-up shows immediately after I step past the End Sub in the BeforeSave event. I'm in fact looking at my settings as I do and the workbook has the saved property set to TRUE, but it's still giving me that pop-up.



Cheers,

Roel

 

[PHV]

both cases are essentially the same.
I disagree.
Your code:
If Not strSaveAsName = [!]"[/!]False[!]"[/!]
mine:
If strSaveAsName <> False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rofeu]

yes, I know, but your code errors out. strSaveAsName is a string not a boolean. Still, if I hit cancel when in GetSaveAsFileName strSaveAsName will be "False", if I put it in a boolean it will be False, upon testing, both your code and mine will result in TRUE, thus continue with the code after Then --> in effect no change to what the code needs to do.

If I'm missing your point completely, please describe the effect of the change you suggest in relation to my problem with the recurring pop-up as occurs with you.



Cheers,

Roel

 

[combo]

The GetSaveAsFileName method returns String or Boolean. Just test it with:
MsgBox TypeName(Application.GetSaveAsFilename)
So the solution is to dim strSaveAsName as Variant and compare it to False, as PHV points. You NEVER get "False" as this method's result (maybe when user write such text as suggested filename...).

Looks like you could simplify the logic of code. If you do not intend to save worbook with 'x' button, the 'BeforeClose' event procedure could be:
ThisWorkbook.Saved=True
and there would be no question concerning saving.

combo

 

[Rofeu]

Hi,

I understand what you are saying, but if I do SaveAs and click Cancel, the value of strSaveAsName is in fact "False".

I'm looking at it in the watch-window and that says "False"
Also testing (strSaveAsName = "False") returns True, but that's not where my problem lies.

I do want the pop-up asking to save upon closing the file (it doesn't just happen when using the 'x' button, but with every way of closing the file), but it keeps on repeating the pop-up after clicking 'Yes' the first time, while it should just close after that. (even Thisworkbook.Saved = True and I still get it)

Just closing the file without saving if changes occurred is not really an option for me.

Cheers,

Roel

 

[combo]

I think that (without using DisplayAlerts) you need to leave 'BeforeClose' event with Saved=False, the state at this moment is used until Close event is finished.

An alternative:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call HandleSaveClose(True)
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call HandleSaveClose(False, True, SaveAsUI)
End Sub

Private Sub HandleSaveClose(byXButton As Boolean, Optional bySave As Boolean = False, Optional bSaveAsUI As Boolean = False)
' all code here
' disable events when saving
ThisWorkbook.Saved = True
End Sub

combo

 

[firefytr]

Paste ALL your code from your ThisWorkbook module. Trust me, EnableEvents is what you're looking for.

-----------
Regards,
Zack Barresse

 

[Rofeu]

Combo, that's the way to go! Have a star from me.

I did have to write code to give the Save/Close/Cancel option, but the trick was indeed to put the code for the BeforeSave event in a seperate sub so that the BeforeSave event would not be called on saving on close. Cancel needs to be set to True in the BeforeSave event, btw.

My end result is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If wbBankBalance.Saved = False Then
        Select Case MsgBox("Do you wish to save the changes to " & wbBankBalance.Name & "?", vbQuestion + vbYesNoCancel)
            Case vbYes
                wbBankBalance.Saved = HandleSaveClose(True)
            Case vbCancel
                Cancel = True
            Case vbNo
        End Select
    End If
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    wbBankBalance.Saved = HandleSaveClose(False, True, SaveAsUI)
    Cancel = True
End Sub

Private Function HandleSaveClose(blnClosing As Boolean, Optional bySave As Boolean = False, Optional bSaveAsUI As Boolean = False) As Boolean

Dim strSaveAsName As String

If wbBankBalance.Saved = True Then Exit Function
If bSaveAsUI = True Then strSaveAsName = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
If strSaveAsName = "False" Then Exit Function

    'Before Save Code

Application.EnableEvents = False

If bSaveAsUI = True Then
    wbBankBalance.SaveAs strSaveAsName
Else
    wbBankBalance.Save
End If

Application.EnableEvents = True

If blnClosing = False Then
        
    'Undo Before Save Code
    
End If
            
HandleSaveClose = (Not strSaveAsName = "False")

End Function

Cheers,

Roel

 

[combo]

Thanks for the star, and you are obviously right about the conversion to "False" string!

combo

 

[firefytr]

Dimension your strSaveAsName as a Variant and use this line ..

If TypeName(strSaveAsName) = "Boolean" Then Exit Function

.. instead of checking for False, it's generally more applicable and stable.

-----------
Regards,
Zack Barresse