PDA

View Full Version : Solved: When closing Workbook repeated message "Do you want to save?"



redseujac
04-27-2011, 10:48 AM
I have the same problem as user "losinj" explained in the following thread: showthread.php?t=22414

Sorry I'm not able to give the complete thread link because this is my first post and thus links are not allowed :(

GTO solved the problem with an attachment given to Colin.

It's a pity I can't get this attachment anymore.

I would appreciate it greatly if GTO could help me with my problem giving me the attachment he gave to Colin.

redseujac
04-28-2011, 10:46 AM
I have the same problem as user "losinj" explained in the following thread: showthread.php?t=22414

Sorry I'm not able to give the complete thread link because this is my first post and thus links are not allowed :(

GTO solved the problem with an attachment given to Colin.

It's a pity I can't get this attachment anymore.

I would appreciate it greatly if GTO could help me with my problem giving me the attachment he gave to Colin.
Is it possible that someone of the moderators PM GTO about this, because I am not able to PM myself because this is my first post.

GTO
04-29-2011, 04:05 AM
Greetings Jacques,

I am sorry I did not see your first post, I am afraid my attendance has been a little spotty lately.

Firstly - Welcome to vbaexpress :-) I am quite sure that you will be glad you joined, as this is a great site. I personally 'lurked' for a long time before joining, and certainly rue my being slow to join, as its been nothing but a blessing. Personally, as much fun as resolving issues, learning new things, and occassionally asking for help myself ("it keeps going KABOOM! Make it stop!") is, I find that 'meeting' some of the folks here, albeit maybe just through writing or an occassional phone call, is just neat. Anyways, I am sure you will get great help here and again, welcome to VBAX.
As to your issue, I read http://www.vbaexpress.com/forum/showthread.php?t=22414 , and am surmising that you want to force the user to enable macros. I believe this is the same that I provided in the other link. I've attached a sample workbook for ease, but here is the code as well, in case we ever lose attachments again.
In losinj's (Colin's) case, I think he wanted workbook and sheet protection, as well as the capability to SaveAs. There may be parts of this that you won't need, but give it a shot and see if we're on the right track.



Option Explicit

'//*************************************************************************** **********//
'// Author: Scott Dennison (The "after save" concept was Scott's and so was 90% of //
'// the original code) //
'// Contributor: Mark Stump (Uhmmm... lucky to be able to assist in working out some //
'// glitches / oversights) //
'// //
'// Acknowledgment: Ken Puhls at www.excelguru.ca (http://www.excelguru.ca) //
'// //
'// Back when first written, the project concept we had made it disadvantageous to //
'// allow a SaveAs. For the poster I went 'looking for' this code for, SaveAs was //
'// desired, and the SaveAs code was 'developed' from his article on the same //
'// subject. This can be seen at: http://www.excelguru.ca/node/91 //
'// //
'// Date: orig: mid 2006? //
'// NOTE1: //
'// I have seen several different examples of how to force the user into having //
'// macros enabled. //
'// Unfortunately, these seem to either: //
'// //
'// 1. Force the user to save upon exiting, as BeforeClose has a Save in it. //
'// While this does more insist that the wb ends up closed with the desired //
'// sheets hidden (and the file is saved, thus opening w/macs disabled should //
'// always result in these sheets being hidden) - there is a price to pay, //
'// in that the user cannot open the wb in a usable state (macros enabled), //
'// make a change, and then decide that he errored and close the wb w/o saving. //
'// Thus - the bad data entered is saved... (Booo!) //
'// //
'// 2. The other manner I've seen this worked out is use the BeforeClose event, //
'// and IF the file is in a saved state when closing, hide the sheets and //
'// resave the wb. This works if the user opens wb, makes changes, saves, then //
'// closes wb. It also works if user opens wb, does or doesn't make changes, //
'// and closes w/o savong. But... If user opens wb, makes changes, saves, //
'// then makes more changes and exits w/o saving... well, the last save //
'// occurred with all the sheets exposed, so you can re-open w/macs disabled //
'// and there's the sheets (eeks!) you were trying to keep hidden. Nuts, eh? //
'// //
'// So... while working on a project that we wanted (1) well protected while //
'// (2) allowing the user to retain the ability to add sheets, we realized that //
'// we could hide all the toolbars we wanted to, eliminate or alter shortcut menus, //
'// replace the menubar, and anything else, but if the user could get the workbook //
'// opened in a usable state with macros disabled, then it was all for naught. //
'// //
'// It was during this that Scott had what I consider to be a bit of an epiphany, //
'// in determining that what was needed was an "AfterSave" event. //
'// //
'// Thus - the following workaround, using the BeforeClose, BeforeSave, and OnOpen //
'// events, essentially provides an 'AfterSave' event //
'// //
'// While I had not run into this solution before, as the Bible says (Song of //
'// Solomon I believe), there is nothing new under the sun - and in fact, I am //
'// finishing this example in regards to an entry of 'losinj' that he later advised //
'// was taken from Ken Puhls' site (See acknowledgment above). //
'// //
'// Anyways, at least to any combination of operations I've thought of, this //
'// appears to prevent the user from being able to side-step enabling macros and //
'// being able to effectively use the workbook. //
'// //
'// Note2: The vba project name has been changed to 'VBAProject_MyCustomProject' for //
'// clarity. //
'// *************************************************************************** *********//

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intResponse As Integer

'// First, we'll kill alerts to prevent the application asking if we want to save //
'// (if wb not saved). We want to replace this with out own 'alert', so that we //
'// can control the save. //
Application.DisplayAlerts = False

'// bolClosing will be false by default, so we will initially pass this test. //
If Not bolClosing Then
'// IF the workbook is saved, it will close without further ado. ELSE, we will //
'// see what the user wants to do and control the results. //
If Not ThisWorkbook.Saved Then
intResponse = MsgBox("Do you want to save the changes you made to '" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & "'?", _
vbExclamation + vbYesNoCancel + vbDefaultButton1, _
"My Custom Project")

Select Case intResponse
Case vbYes
'// bolClosing used in BeforeSave//
bolClosing = True
'// See function. We don't need to execute the save here, and in //
'// fact, due to a weird glitch in Excel (least 2000), this is //
'// better. //
Call Workbook_BeforeSave(False, False)
'//This is required, as even though the file saved while in //
'// BeforeSave, changes occurred post save. //
ThisWorkbook.Saved = True
Case vbNo
bolClosing = True
'// User doesn't want to save changes, so just mark file saved. //
'// This allows us to turn alerts back on and the file to close w/o //
'// alerting. //
ThisWorkbook.Saved = True
Case vbCancel
'// User cancelled closing, and least in Excel 2000, I found it //
'// necessary to reactivate stuff if I wanted the focus returned. //
ThisWorkbook.Activate
bolClosing = False
Cancel = True
Application.DisplayAlerts = True
ActiveCell.Activate
Exit Sub
End Select
End If
End If

Application.DisplayAlerts = True
End Sub

GTO
04-29-2011, 04:14 AM
...also in ThisWorkbook Module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim bolStatusBarIsDisplayedSetting As Boolean, _
intCalculationSetting As Integer, _
wksWorksheet As Worksheet, _
wksLastActive As Worksheet, _
strSaveAs_Filename As String

If Not (bolInProcess _
And Not Cancel) Then

'// Set bolProcess to True, which will later prevent a recurse to this IF. //
bolInProcess = True

'// Let's see what the user's choices were, as to a few settings that we wish //
'// to temporarily control. //
bolStatusBarIsDisplayedSetting = Application.DisplayStatusBar
intCalculationSetting = Application.Calculation

'// Now we'll get rid of StatusBar (as on slower machines it seems to flicker //
'// even w/SreenUpdating False), and we'll stop screen updates and uneccessary //
'// calculations, which will give a better appearance and increase run speed. //
'Application.DisplayStatusBar = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
DoEvents

'// This section along with the counter Protect are optional, depending on if //
'// you want to prevent sheet addition/deletion. //
ThisWorkbook.Unprotect PWORD_WORKBOOK
'// Find the ActiveSheet, so we can redisplay it later. //
Set wksLastActive = ThisWorkbook.ActiveSheet
'// Display the prompt (the warning) sheet BEFORE hidng remaining sheets. //
shtForceEnable.Visible = xlSheetVisible

'// In this example, I only have three sheets. An "INI" sheet that should //
'// always stay hidden, a 'macwarn' sheet that should only display if user //
'// opens wb w/macs disabled, and a user sheet that should only show if //
'// macs are enabled. For larger projects, I would think of prefacing //
'// ea sheet's codename w/ 'shtV' and 'shtH' for visible/hidden sheets and //
'// using LIKE in the loop to determine whether to hide or unhide as the //
'// case may be. //
For Each wksWorksheet In ThisWorkbook.Worksheets
If Not wksWorksheet.CodeName = "shtForceEnable" Then
wksWorksheet.Visible = xlSheetVeryHidden
End If
Next

'// Now we'll reprotect the workbook before actually Saving. //
ThisWorkbook.Protect Password:=PWORD_WORKBOOK, Structure:=True, Windows:=False

'// Set Cancel to True! If the user executed a "normal" save (clicking Save //
'// button or Ctrl+s...), then only called Save is cancelled. A couple of //
'// lines down, we'll execute a Save that will not be cancelled. //
'// If on the other hand, BeforeSave is called programatically, the Cancel does //
'// nothing, and the .Save (or SaveAs) executes under our control. //
'// I would also note that excluding the Cancel results in Saving the file //
'// twice. //
Cancel = True
DoEvents
'// Now, Save workbook. If you step-thru this, you'll see that it immedietely //
'// recurses to Workbook_BeforeSave, but as bolInProcess is currently True, //
'// there is no true recurse. Thus - the statements below the .Save execute, //
'// and the user sheets are redisplayed. //
'// //
'// The critical point here is that the workbook is never in a saved state with //
'// the user sheets visible! //
'// //
'// OK - let's determine whether the user is attempting a saveas. Depending //
'// upon the level of protection desired (file replication, etc), we could //
'// simply cancel the operation - or - if we want to allow a SaveAs, we want //
'// to do the following. //
If SaveAsUI Then
ChDir ThisWorkbook.Path
strSaveAs_Filename = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.FullName, _
FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Are you sure you want to SaveAs?")
'// Check to see if user cancelled; in which case reset bolInProcess and //
'// allow sheets to be redisplayed. Nothing is saved, so no harm, no foul. //
If strSaveAs_Filename = "False" Then
bolInProcess = False
Else
'// If user chose a filename and committed to the saveas, I strip the //
'// proposed name from the proposed fullname, so that we can saveas to //
'// the same folder we opened this workbook in. //
strSaveAs_Filename = "\" & Right(strSaveAs_Filename, _
Len(strSaveAs_Filename) _
- InStrRev(strSaveAs_Filename, "\", -1, _
vbTextCompare))

'// One last check. If user selects same name as current, then cancels //
'// the app alert msg (are your sure? there's already a workbook with //
'// this name...), an error ensues. Just skip past this error, and //
'// file will not be saved. //
On Error Resume Next
ThisWorkbook.SaveAs ThisWorkbook.Path & strSaveAs_Filename
Err.Clear
On Error GoTo 0
End If
Else
ThisWorkbook.Save
End If

'// IF we were closing the workbook when we decided to save, we'll leave //
'// everything hidden, reset settings, turn screen updating back on, //
'// and let the workbook close. //
If bolClosing = True Then
Application.DisplayStatusBar = bolStatusBarIsDisplayedSetting
Application.Calculation = intCalculationSetting
Application.ScreenUpdating = True
Exit Sub
End If

'// This is where we effectively created the "AfterSave" event/workaround. //
'// AFTER saving, we redisplay the sheet(s) that we want the user to be able to //
'// access. //

ThisWorkbook.Unprotect Password:=PWORD_WORKBOOK
'// Redisplay whichever sheets you want the user to be able to see. //
For Each wksWorksheet In ThisWorkbook.Worksheets
If Not wksWorksheet.CodeName = "shtINI" Then
'// Worksheet protection is optional of course. I figure you're //
'// probably using it in any project you'd be hiding sheets in. I //
'// would note that the arg UserInterfaceOnly is handy, as it //
'// allows other areas of your code to effect sheets w/o having to //
'// unprotect/make changes/reprotect. //
wksWorksheet.Protect Password:=PWORD_SHEET, DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True
wksWorksheet.Visible = xlSheetVisible
End If
Next

'// THEN hide Prompt sheet AFTER redisplaying desired Worksheets. //
shtForceEnable.Visible = xlSheetVeryHidden

'// Oops! When we redisplayed sheets, we ended up inadvertantly selecting //
'// whatever sheet became visible in the last rotation thru the loop; so, //
'// let's select the sheet the user was viewing prior to the save. //
If Not ActiveSheet.Name = wksLastActive.Name Then
wksLastActive.Select
End If
'// Reprotect wb if you don't want sheets deleted/added. //
ThisWorkbook.Protect Password:=PWORD_WORKBOOK, Structure:=True, Windows:=False
'// You could also scroll to the last sheet, or where you want to be. This does //
'// not effect which sheet is actually selected; it just puts which tabs you want //
'// in view. //
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

'// Finally - we'll ensure that Excel controls the StatusBar text, updates/refreshs //
'// the screen, and tells Excel that everything is saved. //
'// Now, since we told Excel that it was saved, if the user then decides to close //
'// (and hasn't made any changes), the user will not be asked to save. On the //
'// other hand, if the user makes more changes, then he will be asked if he wants //
'// to save. If he... //
'// chooses yes: this routine will again be run, so that the workbook is //
'// again saved while all the sheets (except the sheet that //
'// advises that macros must be enabled) are again hidden, and //
'// our faux "AfterSave" will again display the sheets you //
'// wanted to be accessable will redisplay. //
'// //
'// chooses no: The workbook will close w/o saving. //
'// As every time the workbook is saved while macros are //
'// enabled, only the "MacWarn" sheet is left visible, AND, //
'// anytime that the workbook is opened with macros disabled //
'// and it is saved, it is saved with only the "MacroWarn" //
'// sheet displayed - there is now no easy way for the user //
'// to not allow the macros to run and use the workbook! //

Application.StatusBar = False
Application.DisplayStatusBar = bolStatusBarIsDisplayedSetting
Application.Calculation = intCalculationSetting
Application.ScreenUpdating = True
ThisWorkbook.Saved = True

'// Reset bolInProcess for the next time that user saves file. //
bolInProcess = False
End If
End Sub

Private Sub Workbook_Open()
Dim wksWorksheet As Worksheet

ThisWorkbook.Unprotect Password:=PWORD_WORKBOOK
'// Redisplay whichever sheets you want the user to be able to see. //
For Each wksWorksheet In ThisWorkbook.Worksheets
If Not wksWorksheet.CodeName = "shtINI" Then
wksWorksheet.Protect Password:=PWORD_SHEET, DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True
wksWorksheet.Visible = xlSheetVisible
End If
Next

'// then hide Prompt sheet AFTER redisplaying desired Worksheets. //
shtForceEnable.Visible = xlSheetVeryHidden

ThisWorkbook.Protect Password:=PWORD_WORKBOOK, Structure:=True, Windows:=False

'// Mark workbook as saved, in case the user decides to close w/o making any //
'// changes. This way, if the user opens and closes the workbook w/o making any //
'// changes, he isn't uneccessarily asked about saving changes. //
ThisWorkbook.Saved = True
End Sub

Public Sub Workbook_CallSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'// I have always noted a weird glitch, at least when //
'// run in OS:XP / Excel: 9.0 (2000). //
'// //
'// In short, the 'force enable' workaround functions correctly when the workbook //
'// is saved 'normally' by the user; that is - the user presses the save button or //
'// keys in the CTRL + s shortcut key combination. //
'// //
'// However, if the running program initiates the save (let's say it runs into a //
'// ThisWorkbook.Save), then if you follow (step-through) the code, you will see //
'// that at line 203, rather than immedietely jumping back to line 63 (the //
'// beginning of 'Workbook_BeforeSave') as it should, it just continues and doesn't //
'// actually save. I have no explanation for this... //
'// //
'// Thus - either through changing 'Workbook_BeforeSave' to a public sub, or, by //
'// adding a procedure such as this, and then substituting a call such as: //
'// 'ThisWorkbook.Workbook_CallSave False, False' for ThisWorkbook.Save, I ensure //
'// that the workbook is actually saved. //
Call Workbook_BeforeSave(False, False)
End Sub
In a Standard Module:

Option Explicit

Public Const PWORD_WORKBOOK As String = "MyWorkbookPasswordAsString"
Public Const PWORD_SHEET As String = "MySheetPasswordAsString"

Public _
bolInProcess As Boolean, _
bolClosing As Boolean

Sub ExecSaveProgramatically()
Call ThisWorkbook.Workbook_CallSave(False, False)
End Sub
Hope that helps,

Mark

redseujac
04-29-2011, 10:18 AM
In losinj's (Colin's) case, I think he wanted workbook and sheet protection, as well as the capability to SaveAs.
First of all thank you for the kind welcome words. Also many thanks for the code you provided.

My problem is not exactly what Colin wanted to achieve (sheet protection and SaveAs).

I just want the user to give the opportunity to close my workbook and exit Excel at the same time by clicking the worksheet button "Quit MyProgram".

Here's my code:

Private Sub cmdCloseMyProgram_Click()

Dim Response As VbMsgBoxResult, Ans As VbMsgBoxResult
Dim iConfig As Integer
Dim sMsg As String, sTitle As String

If rLang.Value = 1 Then
sTitle = wks2.Range("G3")
sMsg = wks2.Range("G4")
ElseIf rLang.Value = 2 Then
sTitle = wks2.Range("H3")
sMsg = wks2.Range("H4")
End If

iConfig = vbYesNo + vbExclamation + vbDefaultButton2
Response = MsgBox(sMsg, iConfig, sTitle)

If Response = vbNo Then Exit Sub

If Not ActiveWorkbook.Saved Then

If rLang.Value = 1
Then sTitle = wks2.Range("G5")
sMsg = wks2.Range("G6")

Else

sTitle = wks2.Range("H5")
sMsg = wks2.Range("H6")

End If

iConfig = vbYesNoCancel + vbExclamation + vbDefaultButton2

Ans = MsgBox(sMsg, iConfig, sTitle)

Select Case Ans

Case vbYes
ActiveWorkbook.Save

Case vbNo
ActiveWorkbook.Saved = True

Case vbCancel
Cancel = True
Exit Sub

End Select

End If

Application.Quit

End Sub


Depending on the language selection the messages "Do you really want to quit the application?" and if so "Do you want to save the changes made to MyProgram?" are displayed in Dutch or in French.

When the user has choosen the Dutch language ("rLang.Value = 1") I have no problems, but when French has been selected ("Else") even when the user anwers "No" to the last question, the same message is displayed again, i.e. "Do you want to save the changes...". That's precisely the problem I wanted to be resolved.

I will try to use the part of your code concerning exiting Excel, but I noticed there's not too much difference with the code I am using.

When my problem is not solved, I will be back here again.

redseujac
04-29-2011, 10:46 AM
Unfortunately nothing changes even after inserting the line
Application.DisplayAlerts = False

like this


...
...

If Response = vbNo Then Exit Sub


Application.DisplayAlerts = False


If Not ActiveWorkbook.Saved Then
If rLang.Value = 1 Then

sTitle = wks2.Range("G5")
sMsg = wks2.Range("G6")

Else

sTitle = wks2.Range("H5")
sMsg = wks2.Range("H6")

End If

iConfig = vbYesNoCancel + vbExclamation + vbDefaultButton2
Ans = MsgBox(sMsg, iConfig, sTitle)

Select Case Ans

Case vbYes
ThisWorkbook.Save

Case vbNo

ThisWorkbook.Saved = True

Case vbCancel

Cancel = True
Exit Sub

End Select

End If

Application.Quit

End Sub

When the French language ("Else") has been selected the message "Do you want to save the changes..." is displayed twice instead of exiting Excel after the first "No" to that message. :(

GTO
04-29-2011, 01:18 PM
Hi Jacques,

I am about gone for the weekend, but for anyone to help, I would suggest attaching a copy or reasonable facsimile of the workbook. We need to see what stuff like "rLang.Value" is to be able to see what the code is doing, or not doing as the case may be.

Of course if it is overly large and/or contains sensitive info, alter to remove.

Mark

redseujac
04-30-2011, 08:19 AM
I solved the problem :clap:

I tried to suppress the Excel prompt to save changes when closing my workbook and exiting Excel when clicking a workbook button.

The following did NOT work as expected:


Application.DisplayAlerts = False


Application.Quit

I changed the code like this:


On Error Resume Next


With Application


.EnableEvents = False


.DisplayAlerts = False


.Quit


End With

Now it works perfectly :)

I don't get another (second) prompt to save changes anymore :whistle:

Anyway, thanks again for your ready help :bow: