Closing a workbook by a button only

[Libby-xl97]

Hi

I have a spreadsheet which is updated and saved by the
user clicking a button on the sheet and then responding to
a form. However, if the workbook is closed by using the x
then the updates are not made.

To counter this, I've disabled the x so that the workbook
can only be closed by following my intended route.

There are two buttons on the form, Close and Back. Close
closes the workbook and back unloads the form but keeps
the workbook open.

This is my code so far
This prevents the x from working when they open the
workbook, thus preventing the user from closing it.

Public ConTrolClose

Private Sub Workbook_Open()
ConTrolClose = True
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Select Case ConTrolClose
Case True
Cancel = True 'cant close workbook
Case False
Cancel = False ' can close workbook
End Select
End Sub


After the user has clicked the button on the sheet, the
updates will be made the form will appear with the option
to go back to the sheet or close

Private Sub cmdClose_Click()
ConTrolClose = False
Application.Quit
End Sub

Private Sub cmdBack_Click()
Unload Me
ConTrolClose = True
End Sub

The problem occurs if the user clicks the Back button. For
some reason the x then closes the workbook, despite my
ConTrolClose being set to True.

Any ideas?

Libby

 

[Jase]

At a punt does it help to switch the two calls in the
cmdback procedure. So you set ControlClose before you
unload me?

Jase

 

[Libby]

Hi Jase

I tried what you suggested which made sense.
Also, the code was in a bit of a state and I had a
BeforeSave setting the ControlClose to False which
probably didn't help.
Anyhow it works now!

Thanks very much
Libby