Save as on exit

[D Willett]

My following code via my VB6 application does as it says on the tin. It opens an Excel worksheet from the specified path.

Code: Select all

Private Sub procDash_Click(Index As Integer)
    
If DSNExists("Af") Then
    Dim SExcel As New Excel.Application
            SExcel.Workbooks.Open ("L:\Af-Dash\Dashboard-MR.xls")
                SExcel.Visible = True
        Else
            MsgBox "Af Does Not Exist On This Machine", vbInformation, "No Af"
End If

End Sub

On closing the worksheet I have code to prompt a save of the worksheet:

Code: Select all

Sub CloseForceSave()
    ThisWorkbook.Close savechanges:=True
End Sub

From one pc the sae function works fine and closes the worksheet.
From anther pc I'm prompted for a "Save As" which is really annoying.
Perhaps I need a more efficient way to close the worksheet which will work uniformally over various pc's.

Any suggestions ?

Cheers

[HansV]

How exactly do you call the CloseForceSave procedure?

[D Willett]

Hi Hans
I have this in the "ThisWorkbook" which is called from a command button:

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn the scroll bars on before closing the workbook.
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With

 Dim lReply As Long
     lReply = MsgBox("Select Yes To Save Details & Exit", vbYesNo, "Exit")
     If lReply = vbYes Then
        ThisWorkbook.Save
        Application.Quit
        Else
        Application.Quit
    End If
End Sub

And this in "Module1"

Code: Select all

Sub CloseForceSave()
    ThisWorkbook.Close savechanges:=True
  
End Sub

[HansV]

Do you mean that CloseForceSave is called from a command button? The Workbook_BeforeClose event procedure cannot be called from a command button.

[D Willett]

To be honest Hans, I can't see if CloseForceSave is used, but i definately call Private Sub Workbook_BeforeClose(Cancel As Boolean) from a command button.
Are my close events clashing?

[HansV]

What is the code behind the command button?

[D Willett]

Appologies:
It looks like I am calling the macro "CloseForceSave" from the command button ( forms tools ).
The Workbook uses :

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn the scroll bars on before closing the workbook.
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With

 Dim lReply As Long
     
    lReply = MsgBox("Select Yes To Save Details & Exit", vbYesNo, "Exit")
     
    If lReply = vbYes Then
        ThisWorkbook.Save
        Application.Quit
        Else
        Application.Quit
    End If
End Sub

Before it closes.

[HansV]

I suspect the problem is that you save the workbook in two places: first in CloseForceSave, by calling

ThisWorkbook.Close SaveChanges:=True

This in turn causes the Workbook_BeforeClose event to fire, where you save the workbook again.

Since you already have SaveChanges:=True in the code behind the command button, you don't have to save the workbook in the Workbook_BeforeClose event.

[D Willett]

Ok I'll take out the offending code and see what happens.
Cheers

[D Willett]

Looka like it's solved it Hans !!

Cheers again..