Solved: Prompt to save on close (askes if already saved?)

[mperrah]

I pulled this code from another post.
I modified it to direct the user to enable macros on open,
and it hides sheets on close to stop the user from working if the macros are not enabled.
On close, even if I've saved my work, it asks to save the file.
I see that it does this because of the contents in [A1000] change if saved,
and that new change prompts the new save.
Will screenupdating and display alerts set to false allow the save without prompts and perform the file changes on close?
here is the code so far...
- thank you in advance -
[VBA]Private Sub Workbook_Open()
Dim Sheet As Worksheet
'make all sheets visible
For Each Sheet In Worksheets
If Sheet.Name <> "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next Sheet
'hide the prompt and go to A1 on sheet1
Sheets("Prompt").Visible = xlSheetVeryHidden
'Application.GoTo Sheets("QCDetail").[A1], Scroll:=True
'clean up
Set Sheet = Nothing
ActiveWorkbook.Saved = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("Prompt")
'if book is already saved, make a note of it
If ActiveWorkbook.Saved = True Then .[A1000] = "Saved"
'make prompt sheet visible
Dim Sheet As Worksheet
.Visible = xlSheetVisible
'hide all other sheets
For Each Sheet In Worksheets
If Sheet.Name <> "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next Sheet
'if the book is already saved, delete
'the previous note and close the book
If .[A100] = "Saved" Then
.[A1000].ClearContents
ActiveWorkbook.Save
End If
'clean up
Set Sheet = Nothing
End With
End Sub
[/VBA]

[Bob Phillips]

How about changing

[vba]

If ActiveWorkbook.Saved = True Then .[A1000] = "Saved"
[/vba]

to

[vba]

If ActiveWorkbook.Saved Then
Range("A1000").Value = "Saved"
ActiveWorkbook.Save
End If
[/vba]

or maybe trap the save eevent and write it before the save happens.

[mperrah]

The code looks like if the file is saved it adds a note in A1000 "saved"
then hides sheets for when it next opens
then deletes the note in A1000 and asks to save again.
Can we just test if the file is saved on close and prompt if it's not
and allow the close if it is already saved?
If we get ride of the helper cell note and just test for the file state as saved or not and prompt for save or allow the close.
The main thing that needs to happen is the sheets other than "prompt" get hidden on close, and on open the user only gets acces to the prompt sheet if the macros are disabled, and the prompt hides and all else unhide when macros are enabled...

[Bob Phillips]

Didn't you write this code then? Assuming not, why not just cut the crap out and use what you want?

[mperrah]

I'm still learning VBA and I'm not confident about everything that happens in the code. I don't know if the "saved" line helps a step in the process.
I pulled out what I don't think I need, but I'm not sure how to avoid the save prompt if they have already saved.
I am hidding sheets, and that change prompts for a save.
I hid screen updating and display alerts but not sure what I'm missing...
[VBA]
Private Sub Workbook_Open()
Dim Sheet As Worksheet
'make all sheets visible
For Each Sheet In Worksheets
If Sheet.Name <> "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next Sheet
'hide the prompt sheet
Sheets("Prompt").Visible = xlSheetVeryHidden
'clean up
Set Sheet = Nothing
' ActiveWorkbook.Saved = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("Prompt")

Application.ScreenUpdating = False
Application.DisplayAlerts = False


'make prompt sheet visible
Dim Sheet As Worksheet
.Visible = xlSheetVisible
'hide all other sheets
For Each Sheet In Worksheets
If Sheet.Name <> "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next Sheet

Set Sheet = Nothing
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
[/VBA]

[Bob Phillips]

You are hiding and making visible sheets, so why not just save the file immediately afterwards. That way you don't get the message, and also if they say no, your changes are irrelevant.

[mperrah]

[vba]Private Sub Workbook_Open()
Dim Sheet As Worksheet
'make all sheets visible
For Each Sheet In Worksheets
If Sheet.Name <> "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next Sheet
'hide the prompt
Sheets("Prompt").Visible = xlSheetVeryHidden
'clean up
Set Sheet = Nothing
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Sheets("Prompt")
'make prompt sheet visible
Dim Sheet As Worksheet
.Visible = xlSheetVisible
'hide all other sheets
For Each Sheet In Worksheets
If Sheet.Name <> "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next Sheet
'clean up
Set Sheet = Nothing
End With
ActiveWorkbook.Save
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub [/vba]

Problem here is it saves changes without asking on close
It should ask only if not saved, and ignore if already saved
(running macro to hide sheets for both - yeah!)

[Bob Phillips]

You are missing my point.

You are changing the workbook when you hide/unhide sheets, so it automatically isn't saved. If you leave it to the user to decide whether to save it or not, they can say no, so you work is ignore.

[Bob Phillips]

But yes, that was what I was suggesting.

[mperrah]

My only concern with this method is saving an unwanted change
I click to close a file expecting to see the save dialoge I can say no to and re-open the file and start off before I made the last changes.
One of the macros I use deletes a sheet, if I run that accidently I can't go back,
so I close and re-open not saving the mess-up.
-I'm re-thinking the reason for the post out loud bear with me...

(case 1) - I think if the file can test if it has been saved, then updating the hidden sheets, re-saving and closing with-out prompt will be good.
(case 2) - if it tests not saved, we should prompt to save and then update the hidden sheets on close.
(case 3) - If the file tests not saved and the user declines save we should close without save or altering anything.

[Bob Phillips]

So how are you going to not save an unwanted change, yet still change sheet visibility and save that. It's all or nothing I think. Either that or you ask the save question yourself, and if they say no then re-open the file, mess with visibility, and then save it.

[mperrah]

If I havent changed the file and close, having not made any changes
it won't matter. the sub can run to re-hide..

If changes were made and saved, (or no changes and saved)
then on close the sheets should re-hide and that change should be saved automaticaly without a prompt

If I made a change but haven't saved it and don't want to -
I should be able to close without saving changes - exit sub

I think maybe the note if saved might help this dilema (A1000="saved")
If saved while open we can test for that cell value on close
and run the script if saved or no changes were made,
prompt if changed but not saved - if save declined close not saving
or if save accepted run sub and save...

[johnske]

Methinks your main problem is that you have changed one address but not the other, consider what you have below - these both need to be the same address
[VBA]
If .[A100] = "Saved" Then
.[A1000].ClearContents
[/VBA]

[mperrah]

I am having trouble with this revision
It hangs at the last elseif. VB says I used an else without an if...
I italicised the parts I think pair up, unless I'm not reading it right?
Also, am I close on the msgbox?
[vba]
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Sheets("Prompt")
'if book is already saved, make a note of it
If ActiveWorkbook.Saved = True Then .[A1000] = "Saved"
'make prompt sheet visible
Dim Sheet As Worksheet
.Visible = xlSheetVisible
'hide all other sheets

For Each Sheet In Worksheets
If Sheet.Name <> "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next Sheet

'if the book is already saved, delete
'the previous note and close the book
If .[A1000] = "Saved" Then
.[A1000].ClearContents

End If
'clean up
Set Sheet = Nothing

ActiveWorkbook.Save

ElseIf ActiveWorkbook.Saved = False Then MsgBox "Save changes? vbyesnocancel"

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End With
End Sub
[/vba]

[Bob Phillips]

Your first IF is fully contained within one line, so there is no Else ... Endif to associate with it. Therefore that ElseIf lower down is not connected to an If (and has no EndIf) anyway.

[johnske]

That code is the old code, it has been revised to also cater for chart sheets, the revision's here... Pls don't change anything, just copy and paste it, the code's self-contained and ready to go as it is so there was absolutely no point changing A100 to A1000. The only thing that should be on the prompt sheet is a message to enable macros and the only thing that may possibly need changing is the name of the "Prompt" sheet (i.e. if you don't like the name "Prompt" and want to name it as something else)

PS if you do need to change things such as addresses, don't rely on your eye finding all instances, use the "find and replace" tool in the VBE window to be thorough about it.

EDIT: Oh, and by the way, the line you've just added (copied below) is so totally unnecessary, if it's not saved Excel will automatically ask if you want to save...
[VBA]
ElseIf ActiveWorkbook.Saved = False Then MsgBox "Save changes? vbyesnocancel"
[/VBA]

[rory]

I still think you have missed xld's point. Consider this:
1. You open the workbook, then sheets get unhidden. No problem.
2. You make some changes. Fine
3. You decide you don't want those changes so want to close the workbook without saving. At this point, your code cannot simply hide the sheets again and save the workbook because that will save the changes you don't want. So you have two choices:
i) You close the workbook unsaved, reopen it, hide the sheets and then save and close it.
ii) You close the workbook without hiding the sheets and hope that macros are enabled next time it is opened.

Regards,
Rory

[johnske]

I still think you have missed xld's point. Consider this:
1. You open the workbook, then sheets get unhidden. No problem.
2. You make some changes. Fine
3. You decide you don't want those changes so want to close the workbook without saving. At this point, your code cannot simply hide the sheets again and save the workbook because that will save the changes you don't want. So you have two choices:
i) You close the workbook unsaved, reopen it, hide the sheets and then save and close it.
ii) You close the workbook without hiding the sheets and hope that macros are enabled next time it is opened.

Regards,
Rory

That's quite incorrect - I think you'd better try the zipped eaxample in the link I gave. (The sheets are first hidden and then you are asked if you want to save the changes).

[rory]

I haven't yet looked at the zip file, but I apologise for not making clear to whom I was talking. My comments were aimed at mperrah, not at you! I should have specified that, so I'm sorry for the confusion.
Rory

[rory]

Having had a look, there still appears to be one problem, unless I've missed something. It seems to fail if:
1. You open the workbook, make some changes and save it.
2. You make some more changes, decide you don't want those saved so you close it.
3. Reopen without enabling macros, and the worksheets are still visible.
Probably not much of an issue, but it is there...
Regards,
Rory