| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving option
I'm using the following code for saving on closing.
But, when I click Yes to save changes it's not saving it. Could anybody help? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim logReadOnly As Boolean 'Turn off events to prevent unwanted loops Application.EnableEvents = False logReadOnly = False 'Evaluate if workbook is saved and emulate default propmts With ThisWorkbook If Not .Saved Then Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _ vbYesNoCancel + vbExclamation) Case Is = vbYes 'Call customized save routine If ActiveWorkbook.ReadOnly Then logReadOnly = True MsgBox ("The Application is read-only. You cannot save changes.") Else Call CustomSave End If Case Is = vbNo 'Do not save logReadOnly = True Case Is = vbCancel 'Set up procedure to cancel close Cancel = True End Select End If 'If Cancel was clicked, turn events back on and cancel close, 'otherwise close the workbook without saving further changes If Not Cancel = True Then .Saved = True Application.EnableEvents = True If logReadOnly Then .Close savechanges:=False Else .Close savechanges:=True End If Else Application.EnableEvents = True End If End With End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Turn off events to prevent unwanted loops Application.EnableEvents = False 'Call customized save routine and set workbook's saved property to true '(To cancel regular saving) Call CustomSave(SaveAsUI) Cancel = True 'Turn events back on an set saved property to true Application.EnableEvents = True ThisWorkbook.Saved = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
saving option
Alex,
Yes, it does not save the workbook. However, it calls another sub "CustomSave",which I assume is supposed to contain the code to save the workbook. The "CustomSave" code is not shown in your post. Jim Cone San Francisco, USA "Alex" wrote in message I'm using the following code for saving on closing. But, when I click Yes to save changes it's not saving it. Could anybody help? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim logReadOnly As Boolean 'Turn off events to prevent unwanted loops Application.EnableEvents = False logReadOnly = False 'Evaluate if workbook is saved and emulate default propmts With ThisWorkbook If Not .Saved Then Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _ vbYesNoCancel + vbExclamation) Case Is = vbYes 'Call customized save routine If ActiveWorkbook.ReadOnly Then logReadOnly = True MsgBox ("The Application is read-only. You cannot save changes.") Else Call CustomSave End If Case Is = vbNo 'Do not save logReadOnly = True Case Is = vbCancel 'Set up procedure to cancel close Cancel = True End Select End If 'If Cancel was clicked, turn events back on and cancel close, 'otherwise close the workbook without saving further changes If Not Cancel = True Then .Saved = True Application.EnableEvents = True If logReadOnly Then .Close savechanges:=False Else .Close savechanges:=True End If Else Application.EnableEvents = True End If End With End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Turn off events to prevent unwanted loops Application.EnableEvents = False 'Call customized save routine and set workbook's saved property to true '(To cancel regular saving) Call CustomSave(SaveAsUI) Cancel = True 'Turn events back on an set saved property to true Application.EnableEvents = True ThisWorkbook.Saved = True End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
saving option
Thanks, Jim.
How could I make it to save on closing if the user chooses Yes (to save)? I cannot see what's interrupting it. I've includede the "CustomSave" in the previous post. "Jim Cone" wrote: Alex, Yes, it does not save the workbook. However, it calls another sub "CustomSave",which I assume is supposed to contain the code to save the workbook. The "CustomSave" code is not shown in your post. Jim Cone San Francisco, USA "Alex" wrote in message I'm using the following code for saving on closing. But, when I click Yes to save changes it's not saving it. Could anybody help? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim logReadOnly As Boolean 'Turn off events to prevent unwanted loops Application.EnableEvents = False logReadOnly = False 'Evaluate if workbook is saved and emulate default propmts With ThisWorkbook If Not .Saved Then Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _ vbYesNoCancel + vbExclamation) Case Is = vbYes 'Call customized save routine If ActiveWorkbook.ReadOnly Then logReadOnly = True MsgBox ("The Application is read-only. You cannot save changes.") Else Call CustomSave End If Case Is = vbNo 'Do not save logReadOnly = True Case Is = vbCancel 'Set up procedure to cancel close Cancel = True End Select End If 'If Cancel was clicked, turn events back on and cancel close, 'otherwise close the workbook without saving further changes If Not Cancel = True Then .Saved = True Application.EnableEvents = True If logReadOnly Then .Close savechanges:=False Else .Close savechanges:=True End If Else Application.EnableEvents = True End If End With End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Turn off events to prevent unwanted loops Application.EnableEvents = False 'Call customized save routine and set workbook's saved property to true '(To cancel regular saving) Call CustomSave(SaveAsUI) Cancel = True 'Turn events back on an set saved property to true Application.EnableEvents = True ThisWorkbook.Saved = True End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
saving option
Alex,
This section of the code doesn't appear correct... I believe you want... "If Cancel = True Then" '----------------- 'If Cancel was clicked, turn events back on and cancel close, 'otherwise close the workbook without saving further changes If Not Cancel = True Then '**** .Saved = True Application.EnableEvents = True If logReadOnly Then .Close savechanges:=False Else .Close savechanges:=True End If Else Application.EnableEvents = True End If End With End Sub '------------------------- Jim Cone San Francisco, USA |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
saving option
Thanks again, Jim.
But, with the changes from Not Cancel = True to Cancel = True it's asking to save it without stopping (some loop). "Jim Cone" wrote: Alex, This section of the code doesn't appear correct... I believe you want... "If Cancel = True Then" '----------------- 'If Cancel was clicked, turn events back on and cancel close, 'otherwise close the workbook without saving further changes If Not Cancel = True Then '**** .Saved = True Application.EnableEvents = True If logReadOnly Then .Close savechanges:=False Else .Close savechanges:=True End If Else Application.EnableEvents = True End If End With End Sub '------------------------- Jim Cone San Francisco, USA |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
saving option
Alex,
Yes, that was a first impression and it wasn't good advice. I've gone back thru the code and have rewritten it some. I had to comment out the Hide and Show AllSheets portion as I don't have that code. I tried this a couple of time and it seems to work. Note the module level variable and the error handling that was added. Jim Cone '-------------------------------- Option Explicit Private blnContinue As Boolean Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error GoTo Err_Handler If blnContinue Then Exit Sub 'Evaluate if workbook is saved and emulate default prompts If Not ThisWorkbook.Saved Then Select Case MsgBox("Do you want to save the changes you made to " _ & ThisWorkbook.Name & "'?", vbYesNoCancel + vbExclamation) Case vbYes 'Call customized save routine If ActiveWorkbook.ReadOnly Then MsgBox ("The Application is read-only. You cannot save changes.") Else 'Turn off events to prevent unwanted loops Application.EnableEvents = False Call CustomSave Application.EnableEvents = True End If Case vbNo 'Do not save blnContinue = True ThisWorkbook.Close savechanges:=False Case vbCancel Cancel = True End Select End If Exit Sub Err_Handler: Application.EnableEvents = True End Sub '----------- Private Sub CustomSave(Optional SaveAs As Boolean) Dim ws As Worksheet, aWs As Worksheet, newFname As String 'Turn off screen flashing Application.ScreenUpdating = False 'Record active worksheet Set aWs = ActiveSheet '**************** ' If ActiveSheet.Name < "Sheet1" Then Call HideAllSheets '**************** 'Save workbook directly or prompt for saveas filename If SaveAs = True Then newFname = Application.GetSaveAsFilename( _ fileFilter:="Excel Files (*.xls), *.xls") If Not newFname = "False" Then ThisWorkbook.SaveAs newFname Else ThisWorkbook.Save End If '**************** 'Restore file to where user was ' Call ShowAllSheets '**************** aWs.Activate 'Restore screen updates Application.ScreenUpdating = True End Sub '---------- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error GoTo Err_Handler 'Turn off events to prevent unwanted loops Application.EnableEvents = False 'Call customized save routine and set workbook's saved property to true '(To cancel regular saving) Call CustomSave(SaveAsUI) Cancel = True 'Turn events back on an set saved property to true Application.EnableEvents = True ThisWorkbook.Saved = True Exit Sub Err_Handler: Application.EnableEvents = True End Sub '------------------------------------------------- "Alex" wrote in message ... Thanks again, Jim. But, with the changes from Not Cancel = True to Cancel = True it's asking to save it without stopping (some loop). |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
saving option
Thank you very much, Jim.
It's working perfectly when I tested it on a new spreadsheet. But, on my workbook on the Main sheet I have a Close button: Private Sub cmdClose_Click() ThisWorkbook.Close End Sub Testing your code on a newly created workbook everything is perfect. But when I'm adding this Close button (as on my app) I'm going to the loop as well. So, I have two choices: get rid of this button or think how to incorporate it properly. Could you advise what's wrong with this button? Thanks "Jim Cone" wrote: Alex, Yes, that was a first impression and it wasn't good advice. I've gone back thru the code and have rewritten it some. I had to comment out the Hide and Show AllSheets portion as I don't have that code. I tried this a couple of time and it seems to work. Note the module level variable and the error handling that was added. Jim Cone '-------------------------------- Option Explicit Private blnContinue As Boolean Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error GoTo Err_Handler If blnContinue Then Exit Sub 'Evaluate if workbook is saved and emulate default prompts If Not ThisWorkbook.Saved Then Select Case MsgBox("Do you want to save the changes you made to " _ & ThisWorkbook.Name & "'?", vbYesNoCancel + vbExclamation) Case vbYes 'Call customized save routine If ActiveWorkbook.ReadOnly Then MsgBox ("The Application is read-only. You cannot save changes.") Else 'Turn off events to prevent unwanted loops Application.EnableEvents = False Call CustomSave Application.EnableEvents = True End If Case vbNo 'Do not save blnContinue = True ThisWorkbook.Close savechanges:=False Case vbCancel Cancel = True End Select End If Exit Sub Err_Handler: Application.EnableEvents = True End Sub '----------- Private Sub CustomSave(Optional SaveAs As Boolean) Dim ws As Worksheet, aWs As Worksheet, newFname As String 'Turn off screen flashing Application.ScreenUpdating = False 'Record active worksheet Set aWs = ActiveSheet '**************** ' If ActiveSheet.Name < "Sheet1" Then Call HideAllSheets '**************** 'Save workbook directly or prompt for saveas filename If SaveAs = True Then newFname = Application.GetSaveAsFilename( _ fileFilter:="Excel Files (*.xls), *.xls") If Not newFname = "False" Then ThisWorkbook.SaveAs newFname Else ThisWorkbook.Save End If '**************** 'Restore file to where user was ' Call ShowAllSheets '**************** aWs.Activate 'Restore screen updates Application.ScreenUpdating = True End Sub '---------- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error GoTo Err_Handler 'Turn off events to prevent unwanted loops Application.EnableEvents = False 'Call customized save routine and set workbook's saved property to true '(To cancel regular saving) Call CustomSave(SaveAsUI) Cancel = True 'Turn events back on an set saved property to true Application.EnableEvents = True ThisWorkbook.Saved = True Exit Sub Err_Handler: Application.EnableEvents = True End Sub '------------------------------------------------- "Alex" wrote in message ... Thanks again, Jim. But, with the changes from Not Cancel = True to Cancel = True it's asking to save it without stopping (some loop). |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
saving option
Alex,
You got me stumped. When the button is clicked, the "ThisWorkbook.Save" code line does not execute. The only thing I can come up with is a 'crutch' that works, but I don't know why. Replace the code for the button... from... ThisWorkbook.Close to....... Application.CommandBars(1).FindControl(ID:=30002). Controls("&Close").Execute Regards, Jim Cone San Francisco, USA "Alex" wrote in message ... Thank you very much, Jim. It's working perfectly when I tested it on a new spreadsheet. But, on my workbook on the Main sheet I have a Close button: Private Sub cmdClose_Click() ThisWorkbook.Close End Sub Testing your code on a newly created workbook everything is perfect. But when I'm adding this Close button (as on my app) I'm going to the loop as well. So, I have two choices: get rid of this button or think how to incorporate it properly. Could you advise what's wrong with this button? Thanks '------------------------------------------ "Jim Cone" wrote: Alex, Yes, that was a first impression and it wasn't good advice. I've gone back thru the code and have rewritten it some. I had to comment out the Hide and Show AllSheets portion as I don't have that code. I tried this a couple of time and it seems to work. Note the module level variable and the error handling that was added. Jim Cone '-------------------------------- Option Explicit Private blnContinue As Boolean Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error GoTo Err_Handler If blnContinue Then Exit Sub 'Evaluate if workbook is saved and emulate default prompts If Not ThisWorkbook.Saved Then Select Case MsgBox("Do you want to save the changes you made to " _ & ThisWorkbook.Name & "'?", vbYesNoCancel + vbExclamation) Case vbYes 'Call customized save routine If ActiveWorkbook.ReadOnly Then MsgBox ("The Application is read-only. You cannot save changes.") Else 'Turn off events to prevent unwanted loops Application.EnableEvents = False Call CustomSave Application.EnableEvents = True End If Case vbNo 'Do not save blnContinue = True ThisWorkbook.Close savechanges:=False Case vbCancel Cancel = True End Select End If Exit Sub Err_Handler: Application.EnableEvents = True End Sub '----------- Private Sub CustomSave(Optional SaveAs As Boolean) Dim ws As Worksheet, aWs As Worksheet, newFname As String 'Turn off screen flashing Application.ScreenUpdating = False 'Record active worksheet Set aWs = ActiveSheet '**************** ' If ActiveSheet.Name < "Sheet1" Then Call HideAllSheets '**************** 'Save workbook directly or prompt for saveas filename If SaveAs = True Then newFname = Application.GetSaveAsFilename( _ fileFilter:="Excel Files (*.xls), *.xls") If Not newFname = "False" Then ThisWorkbook.SaveAs newFname Else ThisWorkbook.Save End If '**************** 'Restore file to where user was ' Call ShowAllSheets '**************** aWs.Activate 'Restore screen updates Application.ScreenUpdating = True End Sub '---------- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error GoTo Err_Handler 'Turn off events to prevent unwanted loops Application.EnableEvents = False 'Call customized save routine and set workbook's saved property to true '(To cancel regular saving) Call CustomSave(SaveAsUI) Cancel = True 'Turn events back on an set saved property to true Application.EnableEvents = True ThisWorkbook.Saved = True Exit Sub Err_Handler: Application.EnableEvents = True End Sub '------------------------------------------------- |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
saving option
Alex,
My reply has not shown up in Outlook Express, so this is a repeat... You got me stumped. When the button is clicked, the "ThisWorkbook.Save" code line does not execute. The only thing I can come up with is a 'crutch' that works, but I don't know why. Replace the code for the button... from... ThisWorkbook.Close to....... Application.CommandBars(1).FindControl(ID:=30002). Controls("&Close").Execute Regards, Jim Cone San Francisco, USA "Alex" wrote in message Thank you very much, Jim. It's working perfectly when I tested it on a new spreadsheet. But, on my workbook on the Main sheet I have a Close button: Private Sub cmdClose_Click() ThisWorkbook.Close End Sub Testing your code on a newly created workbook everything is perfect. But when I'm adding this Close button (as on my app) I'm going to the loop as well. So, I have two choices: get rid of this button or think how to incorporate it properly. Could you advise what's wrong with this button? Thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
saving option
Thank you very much, Jim.
I don't know what this code means but it has resolved all issues. "Jim Cone" wrote: Alex, You got me stumped. When the button is clicked, the "ThisWorkbook.Save" code line does not execute. The only thing I can come up with is a 'crutch' that works, but I don't know why. Replace the code for the button... from... ThisWorkbook.Close to....... Application.CommandBars(1).FindControl(ID:=30002). Controls("&Close").Execute Regards, Jim Cone San Francisco, USA "Alex" wrote in message ... Thank you very much, Jim. It's working perfectly when I tested it on a new spreadsheet. But, on my workbook on the Main sheet I have a Close button: Private Sub cmdClose_Click() ThisWorkbook.Close End Sub Testing your code on a newly created workbook everything is perfect. But when I'm adding this Close button (as on my app) I'm going to the loop as well. So, I have two choices: get rid of this button or think how to incorporate it properly. Could you advise what's wrong with this button? Thanks '------------------------------------------ "Jim Cone" wrote: Alex, Yes, that was a first impression and it wasn't good advice. I've gone back thru the code and have rewritten it some. I had to comment out the Hide and Show AllSheets portion as I don't have that code. I tried this a couple of time and it seems to work. Note the module level variable and the error handling that was added. Jim Cone '-------------------------------- Option Explicit Private blnContinue As Boolean Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error GoTo Err_Handler If blnContinue Then Exit Sub 'Evaluate if workbook is saved and emulate default prompts If Not ThisWorkbook.Saved Then Select Case MsgBox("Do you want to save the changes you made to " _ & ThisWorkbook.Name & "'?", vbYesNoCancel + vbExclamation) Case vbYes 'Call customized save routine If ActiveWorkbook.ReadOnly Then MsgBox ("The Application is read-only. You cannot save changes.") Else 'Turn off events to prevent unwanted loops Application.EnableEvents = False Call CustomSave Application.EnableEvents = True End If Case vbNo 'Do not save blnContinue = True ThisWorkbook.Close savechanges:=False Case vbCancel Cancel = True End Select End If Exit Sub Err_Handler: Application.EnableEvents = True End Sub '----------- Private Sub CustomSave(Optional SaveAs As Boolean) Dim ws As Worksheet, aWs As Worksheet, newFname As String 'Turn off screen flashing Application.ScreenUpdating = False 'Record active worksheet Set aWs = ActiveSheet '**************** ' If ActiveSheet.Name < "Sheet1" Then Call HideAllSheets '**************** 'Save workbook directly or prompt for saveas filename If SaveAs = True Then newFname = Application.GetSaveAsFilename( _ fileFilter:="Excel Files (*.xls), *.xls") If Not newFname = "False" Then ThisWorkbook.SaveAs newFname Else ThisWorkbook.Save End If '**************** 'Restore file to where user was ' Call ShowAllSheets '**************** aWs.Activate 'Restore screen updates Application.ScreenUpdating = True End Sub '---------- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error GoTo Err_Handler 'Turn off events to prevent unwanted loops Application.EnableEvents = False 'Call customized save routine and set workbook's saved property to true '(To cancel regular saving) Call CustomSave(SaveAsUI) Cancel = True 'Turn events back on an set saved property to true Application.EnableEvents = True ThisWorkbook.Saved = True Exit Sub Err_Handler: Application.EnableEvents = True End Sub '------------------------------------------------- |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
saving option
Alex,
You are welcome. Sounds like quite a team, I don't know why and you don't know what.<g The code finds the Close button on the File menu and tells it to run the command. Jim Cone "Alex" wrote in message ... Thank you very much, Jim. I don't know what this code means but it has resolved all issues. "Jim Cone" wrote: Alex, You got me stumped. When the button is clicked, the "ThisWorkbook.Save" code line does not execute. The only thing I can come up with is a 'crutch' that works, but I don't know why. Replace the code for the button... from... ThisWorkbook.Close to....... Application.CommandBars(1).FindControl(ID:=30002). Controls("&Close").Execute Regards, Jim Cone San Francisco, USA |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
saving as option
When using "Save As" a screen "Initializing Folders" shows up, then to my
option where to save. Selecting C: c drive opens but will not allow me to select any thing. I hit cancel and redo save as and this time I can save. This happen in Office 2007 - word and excel. I have this happening on several computers, all different, but with same software. -- Thanks Tom "Alex" wrote: I'm using the following code for saving on closing. But, when I click Yes to save changes it's not saving it. Could anybody help? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim logReadOnly As Boolean 'Turn off events to prevent unwanted loops Application.EnableEvents = False logReadOnly = False 'Evaluate if workbook is saved and emulate default propmts With ThisWorkbook If Not .Saved Then Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _ vbYesNoCancel + vbExclamation) Case Is = vbYes 'Call customized save routine If ActiveWorkbook.ReadOnly Then logReadOnly = True MsgBox ("The Application is read-only. You cannot save changes.") Else Call CustomSave End If Case Is = vbNo 'Do not save logReadOnly = True Case Is = vbCancel 'Set up procedure to cancel close Cancel = True End Select End If 'If Cancel was clicked, turn events back on and cancel close, 'otherwise close the workbook without saving further changes If Not Cancel = True Then .Saved = True Application.EnableEvents = True If logReadOnly Then .Close savechanges:=False Else .Close savechanges:=True End If Else Application.EnableEvents = True End If End With End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Turn off events to prevent unwanted loops Application.EnableEvents = False 'Call customized save routine and set workbook's saved property to true '(To cancel regular saving) Call CustomSave(SaveAsUI) Cancel = True 'Turn events back on an set saved property to true Application.EnableEvents = True ThisWorkbook.Saved = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dont have secondary axis option listed in Series Option | Charts and Charting in Excel | |||
| Saving changes to option to apply all the time. | Excel Discussion (Misc queries) | |||
| preserve formatting option in pivot table option dialog box | Excel Discussion (Misc queries) | |||
| quit without saving option | Excel Programming | |||
| Option Commands (Option Explicit / Option Base etc) - Scope | Excel Programming | |||
Linear Mode
