Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing a workbook by a button only
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing a workbook by a button only
At a punt does it help to switch the two calls in the
cmdback procedure. So you set ControlClose before you unload me? Jase -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing a workbook by a button only
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 -----Original Message----- At a punt does it help to switch the two calls in the cmdback procedure. So you set ControlClose before you unload me? Jase -----Original Message----- 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing a workbook | Excel Discussion (Misc queries) | |||
STOP WORKBOOK CLOSING | Excel Discussion (Misc queries) | |||
Help with closing down a workbook?? | Excel Worksheet Functions | |||
prohibit closing a workbook | Excel Discussion (Misc queries) | |||
Closing a workbook with no alerts | Excel Programming |