×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Code in thread707-923923 not working

Code in thread707-923923 not working

Code in thread707-923923 not working

(OP)
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: workbook password

CODE --> English

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 

RE: Code in thread707-923923 not working

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

CODE

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 wbclosing at this point?


---- Andy

There is a great need for a sarcasm font.

RE: Code in thread707-923923 not working

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.

RE: Code in thread707-923923 not working

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Code in thread707-923923 not working

(OP)
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.

RE: Code in thread707-923923 not working

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

RE: Code in thread707-923923 not working

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Code in thread707-923923 not working

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

This is all to do with 'scope'

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

RE: Code in thread707-923923 not working

To what strongm said, you may try:

CODE

Option Explicit
Dim wbclosing As Boolean

'The rest of your code goes here...
... 
 

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


---- Andy

There is a great need for a sarcasm font.

RE: Code in thread707-923923 not working

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close