saving option

A

Alex

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
 
J

Jim Cone

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"
<[email protected]>
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
 
A

Alex

I forgot to mention that when I'm just saving (whithout closing) it's working.
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
'***************************************************
'Hide all sheets
If ActiveSheet.Name = "Sheet1" Then
Else
Call HideAllSheets
End If
'****************************************************
'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
 
A

Alex

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

Jim Cone

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
 
A

Alex

Thanks again, Jim.
But, with the changes from Not Cancel = True to Cancel = True it's asking to
save it without stopping (some loop).
 
J

Jim Cone

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"
<[email protected]>
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).
 
A

Alex

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
 
J

Jim Cone

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"
<[email protected]>
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
'------------------------------------------
 
J

Jim Cone

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" <[email protected]>
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
 
A

Alex

Thank you very much, Jim.
I don't know what this code means but it has resolved all issues.
 
J

Jim Cone

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"
<[email protected]>
wrote in message
Thank you very much, Jim.
I don't know what this code means but it has resolved all issues.
 
T

Tom

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Making a macro only work on certain files 2
Word macro that can call cells out of Excel for a find and replace 0
Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
Automatically copy with save? 8
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
VBA word change keys 0
Force input in multiple cells in Excel form 8
Disable save/saveas dialog box upon closing 11

Top