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 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 |