I've been on macros that, among other things, will display only one of the
worksheets (containing a macro must be enabled reminder) in the workbook if
the file is opened without macros enabled. If opened with macros enabled, it
will display all the other sheets in the book and hide the reminder sheet.

Apparently I haven't got something quiet right with in either my BeforeSave
or BeforeClose procedure here. It is saving and performing as intended when
doing a Save and a Save As, but is not actually saving the workbook when
going by way of Close/"Do you want to save" Yes. Here is my code below.
This is starting to get to complicated for my green mind to pinpoint the
bugs. TIA for any clue!!!


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Set to run each time the workbook is saved
'Userform is displayed, requiring user to provide
'percent complete and if action items are open.
'Also runs CloseHideSheets procedure so that only
'"Sheet1" with the Macro Enable reminder is
'is visible the next time the workbook is opened
'if it is opened without macros enabled
ThisWorkbook.Sheets("Analysis").Activate
UserForm1.Show
Dim strSaveAs As String
Cancel = True
If SaveAsUI = True Then
'get a save as filename from the user
strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel
Files(*.xls), *.xls", 1)
If strSaveAs = "False" Then
'user cancelled the save as operation
Exit Sub
End If
End If
On Error GoTo Event_Exit
Application.ScreenUpdating = False
Application.EnableEvents = False
CloseHideSheets
If SaveAsUI = True Then
Me.SaveAs strSaveAs
Else
Me.Save
End If
Application.EnableEvents = True
OpenUnhideSheets
ThisWorkbook.Sheets("Analysis").Activate
Event_Exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Set to run each time the workbook is closed
'It allows the user to go back to the working
'tabs if they choose to cancel the close
On Error GoTo Event_Exit
Application.ScreenUpdating = False
Dim Msg As String
If Me.Saved = False Then
Msg = "Do you want to save the changes you made to "
Msg = Msg & Me.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Call CloseHideSheets
Me.Save
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
Me.Saved = True
Event_Exit:
Application.ScreenUpdating = True
End Sub





"JustBreathe" wrote:

Awesome! I tested to see if what you said was happening was and you were
right. Then I was able fit your suggested code in and it is worked just
right on the first try.

....I'm starting to scare myself that I'm actually able to decipher and
apply the advice you folks are able to provide :P

Tanya (newbie)


"Vergel Adriano" wrote:

Hello Tanya,

Cancel=True in the BeforeSave event means cancel the save operation. If
Cancel=False, Excel will proceed with saving your file at the end of the
event. You need to have Cancel=True because you had already saved the
workbook on the previous line and there's no need for Excel to save it again.
Furthermore, if you take that line out, Excel will end up saving your
workbook after you unhide the sheets.

Try your BeforeSave code like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim strSaveAs As String

Cancel = True

If SaveAsUI = True Then
'get a save as filename from the user
strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel Files
(*.xls), *.xls", 1)
If strSaveAs = "False" Then
'user cancelled the save as operation
Exit Sub
End If
End If

'code to hide certain sheets

Application.EnableEvents = False
If SaveAsUI = True Then
Me.SaveAs strSaveAs
Else
Me.Save
End If
Application.EnableEvents = True


'code to unhide certain sheets


End Sub



--
Hope that helps.

Vergel Adriano


"JustBreathe" wrote:

First of all, thanks everyone for the invaluable help you've provided me on
this message board! ...now....

code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'code to hide certain sheets
Me.Save
Cancel = True
'code to unhide certain sheets
End Sub

question: what exactly does the Cancel = True do (or not do)?
I found that when I was trying to Save As a new filename that I was not
being given the option to change the name. when I comment out the Cancel =
True line in my BeforeSave procedure I am able to do save with a new filename
when doing a Save As. I want to understand why this is so, so that I know
that I am not inadvertently causing other potential problems by removing the
Cancel = True.

TIA
Tanya (vba newbie)

Hi Tanya,

I don't think you need to have code for the BeforeClose event. By default,
Excel will prompt the user to save changes (if there's any) when closing the
workbook. If the user says Yes, then your BeforeSave code executes.

If in case you are trapping the BeforeClose event because you want to
display your own message, try the case vbYes part this way:

Case vbYes
If ThisWorkbook.Path = "" Then
'execute the BeforeSave code, prompt for filename
Workbook_BeforeSave True, False
Else
'execute the BeforeSave code
Workbook_BeforeSave False, False
End If

that seemed to work, but I'm not really sure why..




--
Hope that helps.

Vergel Adriano


"JustBreathe" wrote:

I've been on macros that, among other things, will display only one of the
worksheets (containing a macro must be enabled reminder) in the workbook if
the file is opened without macros enabled. If opened with macros enabled, it
will display all the other sheets in the book and hide the reminder sheet.

Apparently I haven't got something quiet right with in either my BeforeSave
or BeforeClose procedure here. It is saving and performing as intended when
doing a Save and a Save As, but is not actually saving the workbook when
going by way of Close/"Do you want to save" Yes. Here is my code below.
This is starting to get to complicated for my green mind to pinpoint the
bugs. TIA for any clue!!!


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Set to run each time the workbook is saved
'Userform is displayed, requiring user to provide
'percent complete and if action items are open.
'Also runs CloseHideSheets procedure so that only
'"Sheet1" with the Macro Enable reminder is
'is visible the next time the workbook is opened
'if it is opened without macros enabled
ThisWorkbook.Sheets("Analysis").Activate
UserForm1.Show
Dim strSaveAs As String
Cancel = True
If SaveAsUI = True Then
'get a save as filename from the user
strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel
Files(*.xls), *.xls", 1)
If strSaveAs = "False" Then
'user cancelled the save as operation
Exit Sub
End If
End If
On Error GoTo Event_Exit
Application.ScreenUpdating = False
Application.EnableEvents = False
CloseHideSheets
If SaveAsUI = True Then
Me.SaveAs strSaveAs
Else
Me.Save
End If
Application.EnableEvents = True
OpenUnhideSheets
ThisWorkbook.Sheets("Analysis").Activate
Event_Exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Set to run each time the workbook is closed
'It allows the user to go back to the working
'tabs if they choose to cancel the close
On Error GoTo Event_Exit
Application.ScreenUpdating = False
Dim Msg As String
If Me.Saved = False Then
Msg = "Do you want to save the changes you made to "
Msg = Msg & Me.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Call CloseHideSheets
Me.Save
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
Me.Saved = True
Event_Exit:
Application.ScreenUpdating = True
End Sub





"JustBreathe" wrote:

Awesome! I tested to see if what you said was happening was and you were
right. Then I was able fit your suggested code in and it is worked just
right on the first try.

....I'm starting to scare myself that I'm actually able to decipher and
apply the advice you folks are able to provide :P

Tanya (newbie)


"Vergel Adriano" wrote:

Hello Tanya,

Cancel=True in the BeforeSave event means cancel the save operation. If
Cancel=False, Excel will proceed with saving your file at the end of the
event. You need to have Cancel=True because you had already saved the
workbook on the previous line and there's no need for Excel to save it again.
Furthermore, if you take that line out, Excel will end up saving your
workbook after you unhide the sheets.

Try your BeforeSave code like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim strSaveAs As String

Cancel = True

If SaveAsUI = True Then
'get a save as filename from the user
strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel Files
(*.xls), *.xls", 1)
If strSaveAs = "False" Then
'user cancelled the save as operation
Exit Sub
End If
End If

'code to hide certain sheets

Application.EnableEvents = False
If SaveAsUI = True Then
Me.SaveAs strSaveAs
Else
Me.Save
End If
Application.EnableEvents = True


'code to unhide certain sheets


End Sub



--
Hope that helps.

Vergel Adriano


"JustBreathe" wrote:

First of all, thanks everyone for the invaluable help you've provided me on
this message board! ...now....

code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'code to hide certain sheets
Me.Save
Cancel = True
'code to unhide certain sheets
End Sub

question: what exactly does the Cancel = True do (or not do)?
I found that when I was trying to Save As a new filename that I was not
being given the option to change the name. when I comment out the Cancel =
True line in my BeforeSave procedure I am able to do save with a new filename
when doing a Save As. I want to understand why this is so, so that I know
that I am not inadvertently causing other potential problems by removing the
Cancel = True.

TIA
Tanya (vba newbie)

Hi Vergel,

I had put in this particular BeforeClose procedure, mainly because what I
was finding was that if the user Canceled the save, the sheets they work in
were already hidden and prevented them from easily going back to working in
the file without confusion.

Thanks for your suggestion here. I will try to start deciphering it here
shortly.

"Vergel Adriano" wrote:

Hi Tanya,

I don't think you need to have code for the BeforeClose event. By default,
Excel will prompt the user to save changes (if there's any) when closing the
workbook. If the user says Yes, then your BeforeSave code executes.

If in case you are trapping the BeforeClose event because you want to
display your own message, try the case vbYes part this way:

Case vbYes
If ThisWorkbook.Path = "" Then
'execute the BeforeSave code, prompt for filename
Workbook_BeforeSave True, False
Else
'execute the BeforeSave code
Workbook_BeforeSave False, False
End If

that seemed to work, but I'm not really sure why..




--
Hope that helps.

Vergel Adriano


"JustBreathe" wrote:

I've been on macros that, among other things, will display only one of the
worksheets (containing a macro must be enabled reminder) in the workbook if
the file is opened without macros enabled. If opened with macros enabled, it
will display all the other sheets in the book and hide the reminder sheet.

Apparently I haven't got something quiet right with in either my BeforeSave
or BeforeClose procedure here. It is saving and performing as intended when
doing a Save and a Save As, but is not actually saving the workbook when
going by way of Close/"Do you want to save" Yes. Here is my code below.
This is starting to get to complicated for my green mind to pinpoint the
bugs. TIA for any clue!!!


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Set to run each time the workbook is saved
'Userform is displayed, requiring user to provide
'percent complete and if action items are open.
'Also runs CloseHideSheets procedure so that only
'"Sheet1" with the Macro Enable reminder is
'is visible the next time the workbook is opened
'if it is opened without macros enabled
ThisWorkbook.Sheets("Analysis").Activate
UserForm1.Show
Dim strSaveAs As String
Cancel = True
If SaveAsUI = True Then
'get a save as filename from the user
strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel
Files(*.xls), *.xls", 1)
If strSaveAs = "False" Then
'user cancelled the save as operation
Exit Sub
End If
End If
On Error GoTo Event_Exit
Application.ScreenUpdating = False
Application.EnableEvents = False
CloseHideSheets
If SaveAsUI = True Then
Me.SaveAs strSaveAs
Else
Me.Save
End If
Application.EnableEvents = True
OpenUnhideSheets
ThisWorkbook.Sheets("Analysis").Activate
Event_Exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Set to run each time the workbook is closed
'It allows the user to go back to the working
'tabs if they choose to cancel the close
On Error GoTo Event_Exit
Application.ScreenUpdating = False
Dim Msg As String
If Me.Saved = False Then
Msg = "Do you want to save the changes you made to "
Msg = Msg & Me.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Call CloseHideSheets
Me.Save
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
Me.Saved = True
Event_Exit:
Application.ScreenUpdating = True
End Sub





"JustBreathe" wrote:

Awesome! I tested to see if what you said was happening was and you were
right. Then I was able fit your suggested code in and it is worked just
right on the first try.

....I'm starting to scare myself that I'm actually able to decipher and
apply the advice you folks are able to provide :P

Tanya (newbie)


"Vergel Adriano" wrote:

Hello Tanya,

Cancel=True in the BeforeSave event means cancel the save operation. If
Cancel=False, Excel will proceed with saving your file at the end of the
event. You need to have Cancel=True because you had already saved the
workbook on the previous line and there's no need for Excel to save it again.
Furthermore, if you take that line out, Excel will end up saving your
workbook after you unhide the sheets.

Try your BeforeSave code like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim strSaveAs As String

Cancel = True

If SaveAsUI = True Then
'get a save as filename from the user
strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel Files
(*.xls), *.xls", 1)
If strSaveAs = "False" Then
'user cancelled the save as operation
Exit Sub
End If
End If

'code to hide certain sheets

Application.EnableEvents = False
If SaveAsUI = True Then
Me.SaveAs strSaveAs
Else
Me.Save
End If
Application.EnableEvents = True


'code to unhide certain sheets


End Sub



--
Hope that helps.

Vergel Adriano


"JustBreathe" wrote:

First of all, thanks everyone for the invaluable help you've provided me on
this message board! ...now....

code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'code to hide certain sheets
Me.Save
Cancel = True
'code to unhide certain sheets
End Sub

question: what exactly does the Cancel = True do (or not do)?
I found that when I was trying to Save As a new filename that I was not
being given the option to change the name. when I comment out the Cancel =
True line in my BeforeSave procedure I am able to do save with a new filename
when doing a Save As. I want to understand why this is so, so that I know
that I am not inadvertently causing other potential problems by removing the
Cancel = True.

TIA
Tanya (vba newbie)

Hi Tanya,

The only time a user can cancel on the save is when it's a Save As
operation. If you present the choice to them first before doing anything
else, then, you'll be able to gracefully exit out of the BeforeSave event
without hiding any sheet yet. Here's another suggestion. Try re-arranging
your BeforeSave code like this to give the user the opportunity to cancel at
the start.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Set to run each time the workbook is saved
'Userform is displayed, requiring user to provide
'percent complete and if action items are open.
'Also runs CloseHideSheets procedure so that only
'"Sheet1" with the Macro Enable reminder is
'is visible the next time the workbook is opened
'if it is opened without macros enabled
Dim strSaveAs As String
Cancel = True
If SaveAsUI = True Then
'get a save as filename from the user
strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel
Files(*.xls), *.xls", 1)
If strSaveAs = "False" Then
'user cancelled the save as operation
Exit Sub
End If
End If

ThisWorkbook.Sheets("Analysis").Activate
UserForm1.Show

On Error GoTo Event_Exit
Application.ScreenUpdating = False
Application.EnableEvents = False
CloseHideSheets
If SaveAsUI = True Then
Me.SaveAs strSaveAs
Else
Me.Save
End If
Application.EnableEvents = True
OpenUnhideSheets
ThisWorkbook.Sheets("Analysis").Activate

Event_Exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub



--
Hope that helps.

Vergel Adriano


"JustBreathe" wrote:

Hi Vergel,

I had put in this particular BeforeClose procedure, mainly because what I
was finding was that if the user Canceled the save, the sheets they work in
were already hidden and prevented them from easily going back to working in
the file without confusion.

Thanks for your suggestion here. I will try to start deciphering it here
shortly.

"Vergel Adriano" wrote:

Hi Tanya,

I don't think you need to have code for the BeforeClose event. By default,
Excel will prompt the user to save changes (if there's any) when closing the
workbook. If the user says Yes, then your BeforeSave code executes.

If in case you are trapping the BeforeClose event because you want to
display your own message, try the case vbYes part this way:

Case vbYes
If ThisWorkbook.Path = "" Then
'execute the BeforeSave code, prompt for filename
Workbook_BeforeSave True, False
Else
'execute the BeforeSave code
Workbook_BeforeSave False, False
End If

that seemed to work, but I'm not really sure why..




--
Hope that helps.

Vergel Adriano


"JustBreathe" wrote:

I've been on macros that, among other things, will display only one of the
worksheets (containing a macro must be enabled reminder) in the workbook if
the file is opened without macros enabled. If opened with macros enabled, it
will display all the other sheets in the book and hide the reminder sheet.

Apparently I haven't got something quiet right with in either my BeforeSave
or BeforeClose procedure here. It is saving and performing as intended when
doing a Save and a Save As, but is not actually saving the workbook when
going by way of Close/"Do you want to save" Yes. Here is my code below.
This is starting to get to complicated for my green mind to pinpoint the
bugs. TIA for any clue!!!


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Set to run each time the workbook is saved
'Userform is displayed, requiring user to provide
'percent complete and if action items are open.
'Also runs CloseHideSheets procedure so that only
'"Sheet1" with the Macro Enable reminder is
'is visible the next time the workbook is opened
'if it is opened without macros enabled
ThisWorkbook.Sheets("Analysis").Activate
UserForm1.Show
Dim strSaveAs As String
Cancel = True
If SaveAsUI = True Then
'get a save as filename from the user
strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel
Files(*.xls), *.xls", 1)
If strSaveAs = "False" Then
'user cancelled the save as operation
Exit Sub
End If
End If
On Error GoTo Event_Exit
Application.ScreenUpdating = False
Application.EnableEvents = False
CloseHideSheets
If SaveAsUI = True Then
Me.SaveAs strSaveAs
Else
Me.Save
End If
Application.EnableEvents = True
OpenUnhideSheets
ThisWorkbook.Sheets("Analysis").Activate
Event_Exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Set to run each time the workbook is closed
'It allows the user to go back to the working
'tabs if they choose to cancel the close
On Error GoTo Event_Exit
Application.ScreenUpdating = False
Dim Msg As String
If Me.Saved = False Then
Msg = "Do you want to save the changes you made to "
Msg = Msg & Me.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Call CloseHideSheets
Me.Save
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
Me.Saved = True
Event_Exit:
Application.ScreenUpdating = True
End Sub





"JustBreathe" wrote:

Awesome! I tested to see if what you said was happening was and you were
right. Then I was able fit your suggested code in and it is worked just
right on the first try.

....I'm starting to scare myself that I'm actually able to decipher and
apply the advice you folks are able to provide :P

Tanya (newbie)


"Vergel Adriano" wrote:

Hello Tanya,

Cancel=True in the BeforeSave event means cancel the save operation. If
Cancel=False, Excel will proceed with saving your file at the end of the
event. You need to have Cancel=True because you had already saved the
workbook on the previous line and there's no need for Excel to save it again.
Furthermore, if you take that line out, Excel will end up saving your
workbook after you unhide the sheets.

Try your BeforeSave code like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim strSaveAs As String

Cancel = True

If SaveAsUI = True Then
'get a save as filename from the user
strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel Files
(*.xls), *.xls", 1)
If strSaveAs = "False" Then
'user cancelled the save as operation
Exit Sub
End If
End If

'code to hide certain sheets

Application.EnableEvents = False
If SaveAsUI = True Then
Me.SaveAs strSaveAs
Else
Me.Save
End If
Application.EnableEvents = True


'code to unhide certain sheets


End Sub



--
Hope that helps.

Vergel Adriano


"JustBreathe" wrote:

First of all, thanks everyone for the invaluable help you've provided me on
this message board! ...now....

code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'code to hide certain sheets
Me.Save
Cancel = True
'code to unhide certain sheets
End Sub

question: what exactly does the Cancel = True do (or not do)?
I found that when I was trying to Save As a new filename that I was not
being given the option to change the name. when I comment out the Cancel =
True line in my BeforeSave procedure I am able to do save with a new filename
when doing a Save As. I want to understand why this is so, so that I know
that I am not inadvertently causing other potential problems by removing the
Cancel = True.

TIA
Tanya (vba newbie)