Code in thread707-923923 not working

[TW VBA starter]

Hi,

I've stumbled upon the code from 2004 in the thread reference below and it's almost exactly what I'm looking for for my project. Unfortunately, I've ran the code and encountered one main issue. The workbook only closes if you click on don't save. If you click save, it will loop and bring you the safe prompt again. Somehow the "if wbclosing = true then" trigger does not work. I also have the suspicion that after "not saving" the splash screen is the only visible sheet at the point of closing. I've also encountered the issue that if the file is saved with only the splash screen visible, even with macros enabled the Workbook_open sub does not run. I've made sure that all code is stored in the workbook and not module or sheet.

I'm using Office 365, could it be that this type of coding won't work anymore?

Thanks in advance for any suggestions.

Cheers,

thread707-923923

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This sub runs when the user tries to close the file
wbClosing = True    'Set the Boolean flag wbXClosing to True. This value is used by SpecialSave sub.
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'This sub runs when the user tries to Save or Save As the file
SpecialSave     'Call the SpecialSave sub
Cancel = True   'Since SpecialSave sub will save the workbook, don't try to repeat it a second time
End Sub


Private Sub SpecialSave()
'This sub runs whenever user tries to save the file
Dim ws As Worksheet, wsSplash As Worksheet, wsCurrent As Worksheet
Dim pswd As String
pswd = "Brad"   'Password to protect the workbook
Application.ScreenUpdating = False  'No screen flicker. Code runs faster if screen doesn't update until macro done
Application.EnableEvents = False    'Stop events handling until end of sub. This prevents recursive calling of event subs.
Set wsSplash = Worksheets("Splash screen")  'Pointer to the Splash screen worksheet
Set wsCurrent = ActiveSheet 'Pointer to the current worksheet
ThisWorkbook.Unprotect Password:=pswd   'Unprotect the workbook using the password stored in string variable pswd
wsSplash.Visible = xlSheetVisible   'Make the Splash screen worksheet visible
For Each ws In ThisWorkbook.Worksheets  'Loop through all the worksheets
    If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVeryHidden   'Make the other worksheets very hidden (only visible through VBA code)
Next ws
ThisWorkbook.Protect Password:=pswd     'Protect the workbook before saving it
ThisWorkbook.Save   'Save the file
If wbClosing = True Then ThisWorkbook.Close 'If trying to close the workbook, allow the process to occur
ThisWorkbook.Unprotect Password:=pswd   'Unprotect the workbook. Will get error when trying to make sheets visible if this not done.
For Each ws In ThisWorkbook.Worksheets  'Loop through each worksheet
    If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVisible  'Make all sheets except Splash screen visible
Next ws
wsSplash.Visible = xlSheetVeryHidden    'Make Splash screen very hidden
wsCurrent.Activate  'Activate the initial worksheet
ThisWorkbook.Protect Password:=pswd 'Restore workbook protection
Application.EnableEvents = True 'Turn events handling back on
Application.ScreenUpdating = True   'Allow the screen to update
End Sub

Private Sub Workbook_Open()
'This sub runs when the workbook opens
Dim ws As Worksheet, wsSplash As Worksheet
Dim pswd As String
pswd = "Brad"   'Password used to protect the workbook
Application.ScreenUpdating = False  'Turn screen updating off. Sub runs faster. No screen flicker.
Application.EnableEvents = True     'Turn events handling off. Avoids recursive calling of events subs
wbClosing = False   'Initialize wbClosing to False
Set wsSplash = Worksheets("Splash screen")  'Pointer to the Splash screen worksheet
ThisWorkbook.Unprotect Password:=pswd       'Unprotect the workbook
wsSplash.Visible = xlSheetVisible   'Make the Splash screen worksheet visible
For Each ws In ThisWorkbook.Worksheets  'Loop through all the worksheets
    If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVisible  'Make worksheets visible
Next ws
wsSplash.Visible = xlSheetVeryHidden    'Make the Splash screen worksheet very hidden (only visible through VBA code)
ThisWorkbook.Protect Password:=pswd     'Protect the workbook with a password
Application.ScreenUpdating = True   'Allow the screen to update
End Sub

 

[Andrzejek]

Is that the whole code you have? I suspect the answer is: no.
I usually see something like:

Option Explicit
...

>Somehow the "if wbclosing = true then" trigger does not work.
How do you know that? Did you step thru your code and examine the value of [tt]wbclosing[/tt] at this point?


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

Sadly, Brad's code in the original thread is incomplete; you needed to download his linked spreadsheet (which is no longer accessible). Andy has pretty much put his finger on it in his post above.

 

[SkipVought]

May I also emphasize the need for you to understand what Closing or Saving means as far as what Event code executes.

In order for you to understand what's happening in your code, you ought to Step through your code, use the Watch Window to observe the values in significant objects and variables.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[TW VBA starter]

Thanks for all your feedback, I know I'm not really going about this the right away. I should rather learn how to use VBA from the bottom then trying to adapt someone's code and trying to implement it for my own projects.

I did try to step through the code and what I notice is that wbclosing goes back to "false" as soon as the subsequent beforesave sub runs. So when I close the workbook (x on the top right), the beforeclose sub runs (wbclosing = True) but right afterwards the save prompt opens and wbclosing is back to "false". I tested this with "if wbclosing = false then msgbox("text")", at various positions of beforeSave and SpecialSave Subs.

I guess if there's something missing and Brad's Spreadsheet can't be recovered, I will have to dig something out. Thanks Andy for pointing me into the Option Explicit direction.

 

[SkipVought]

Faq707-4594

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[TW VBA starter]

Thanks for the link Skip! Sorry to bother you with my basic stuff.

 

[SkipVought]

No bother at all. That's what Tek-Tips is all about.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

>what I notice is that wbclosing goes back to "false"

This is all to do with 'scope'

In essence, because you have not declared [tt]wbclosing[/tt] anywhere (and because you don't appear to have Option Explicit set), each [tt]wbclosing[/tt] is actually a different variable since each of the procedures is creating its own version of the variable on the fly

 

[Andrzejek]

To what strongm said, you may try:

Option Explicit[blue]
Dim wbclosing As Boolean[/blue]
[green]
'The rest of your code goes here...
... 
[/green]

and run your code. Who knows, that may be all what you need...


---- Andy

There is a great need for a sarcasm font.

 

[TW VBA starter]

Thanks a lot the both of you, that was all that was needed!
Cheers,