Part 2, Controlling Save/SaveAs Depending on Document Event

[ac503683]

This is related to the following topic:
http://www.vbaexpress.com/forum/showthread.php?t=43620

The code posted there works well and is more intuitive for the user. I do have a few items I wish to tweak.

The first is to display a message when the user tries to close the document by clicking the "X" on the top right-hand side.

Here is the code for this:

Sub AutoClose()
' Mark the document as unsaved so a prompt
' appears asking whether to save changes.
ActiveDocument.Saved = False

'Note message box is disabled until routine can be stopped when OpenSaveAndSend runs.
'MsgBox "Clicking the Close Button does not work. To save choose 'Save' or 'Save As' command."

'Dismiss the displayed prompt.
SendKeys "{ESC}"
End Sub

I have the message box currently out of commission because of the logic involved in the VBA code.

When the user tries to close the document through "save" or "saveas" it triggers a userform that provides several options to the user in how to save and/or close. When the user chooses to close the document, whether saved or not, the message box in the code above is still displayed because the macro is triggered by the close document event no matter what method is used to close it.

My goal is to display the above message box ONLY when the user clicks on the red "X" and not when the SaveAndSend userform is called into action. Is this possible?

Currently the way the code works appears seamless to the user. And there is the typical "ding" when trying to close the document using the red "X". It would be nice touch to tell the user that it is not working specific to that method.

My second desire is to prevent the userform SaveAndSend and the save/saveas routines from running once the document is saved and then reopened. The document at this point is opened as a protected "read only" with the template still attached. I'm not really sure I want the macros to run in the template either. So the goal is to have all the related save routines posted here http://www.vbaexpress.com/forum/showthread.php?t=43620 to work for a new document but not when the new document is saved and then reopened.

Edit: I think I'm asking if there can be more than one Document_New or Document_Open event? (not sure if I'm asking this correctly)

[Frosty]

I *think* this might be an argument for using a public variable. Something at the top of your module like
Public pub_blOkayToClose As Boolean

And then have your SaveAndSend routine (somewhere) set that public variable to True.

And then in the code above, you wrap your messagebox in using that public variable
If pub_blOkayToClose = False Then
Msgbox "Clicking the Close Button does not work yada yada"
End If

Your other problem is basically the same question-- you need to store a persistent "variable" somewhere. There are a couple of options to this.

1) Public variable (like above).
2) Document variable
3) CustomDocumentProperty (or adaptation of a BuiltinDocumentProperty)
4) Text in the document somewhere
5) text in a file somewhere
6) Registry

That's pretty much all the places you could store information that isn't related to the actual run time of the code... so that you could check that info at various points.

The public variables only last as long Word is open, and you have an issue if there's one public variable and 3 different documents -- although you can have it be an array of document names that are allowed to be closed, etc etc.

The document variables can be document specific, and you can assume if they aren't there, then the document is not okay to be saved. This may be a good "two birds with one stone" solution, in that you can check to see if the doc variable exists or not, has a value of one thing or another... and then act accordingly.

Others are additional options, but I think custom document properties or document variables are the way to go. The bad thing about those two options (as opposed to text hidden in the document somewhere, like an undisplayed even page header), is that they can get lost by various metadata removal processes.

[ac503683]

Thanks again Jason,

Gave it a go.

I declared a public variable as suggested both in ThisDocument and the userform.

For some reason when I threw in the code in the SaveAndSend userform changing the variable to "true", the value isn't saved. It is not saved when I tried other areas such as in the Save or SaveAs procedures.



I tested the value of the boolean variable by changing the "false" to a "true" in the code below. If "true" the message would show, which it didn't. This was placed in the sub AutoClose above. Tried moving the IF statements around with no change in the results.

If pub_blOkayToClose = False Then
Msgbox "Clicking the Close Button does not work yada yada"
End If

Here is an example:


Sub FileSaveAs()
'If MsgBox(prompt:="Are you ready to save and close document?", Buttons:=vbYesNo) = vbYes Then
Call OpenSaveAndSend
DoNotClose = True
'End If
End Sub

Sub AutoClose()

' Mark the document as unsaved so a prompt
' appears asking whether to save changes.
ActiveDocument.Saved = False


If DoNotClose = False Then
'Note message box is disabled until routine can be stopped when OpenSaveAndSend runs.
MsgBox "Clicking the Close Button does not work. To save choose 'Save' or 'Save As' command."
End If

'Dismiss the displayed prompt.
SendKeys "{ESC}"
End Sub


In userform:
Private Sub cmdStop_Click()
DoNotClose = True
'If MsgBox(prompt:="Do you really want to close? No changes will be saved.", Buttons:=vbOKCancel) = vbOK Then
Me.Hide
Application.DisplayAlerts = False
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

'End If
End Sub

The DoNotClose = True is not saved in any of the procedures at this point. The default value remains unchanged.

Regards,
Candice

[Frosty]

I'm confused-- you declared a public variable in multiple places? How did you declare it?

You need to do...
Public pub_blOkayToClose As Boolean
at the top of your regular module... not inside a user form.

Also-- please use prefixes on your variables to indicate both type and scope. Otherwise (that's why I point the "OkayToClose" variable has both "pub_" (for public scope) and "bl" (for boolean type). Your prefixes don't matter (those are my preferences, but the actual letters used aren't important), but consistency does. It will save you hours of debugging.

My guess is you did this
Dim OkayToClose As Boolean
at the top of both your module and your userform... which created a Private scope (modular) variable, independent to both locations (code in the userform, and code in the calling module).

And that's allowed, but not recommended.

EDIT: allowed, but not recommended... and obviously since they are two different variables with two different scopes and lifetimes... this wouldn't be the effect you're looking for.

Also-- I think the doc variable would be better for your other option. Look into ActiveDocument.Variables as the alternative.

[ac503683]

Sorry for the confusion.

I declared a public variable in my regular module and in the general declarations for the userform as the code didn't seem to work otherwise--will check on that.

And I stand corrected about the boolean value not changing or being saved for the variable--found a nice code that displays the native value of the variable.

The variable value does change from "false" to "true" but apparently *after* the save/saveas/autoclose routines run. So how to set the value to "true" *before* the save routines?

Thanks for the nomenclature lesson. Use a similar convention in Access and find it helpful.


EDIT:
Ok just tested again without declaring the public variable as boolean in the userform. I get a "compile error--variable not defined".

[Frosty]

If your userform can't see the variable (which is what it's saying with that "variable not defined" error), then it's not a public variable.

You have to use the Public keyword, not the Dim keyword. What does your actual declaration of the variable look like?

[fumei]

Yes, how exactly are you declaring OkayToClose and DoNotClose?

[ac503683]

If your userform can't see the variable (which is what it's saying with that "variable not defined" error), then it's not a public variable.

You have to use the Public keyword, not the Dim keyword. What does your actual declaration of the variable look like?

Declaration:

Public DoNotClose As Boolean

Based on the suggested code above the variable is declared in both the main document module and in the userform. If the variable is used in a procedure in the userform, I have to declare the variable as well or I get "compile erroro--variable not defined."

[ac503683]

Yes, how exactly are you declaring OkayToClose and DoNotClose?

I have no code for OkayToClose but am using DoNotClose, essentially replacing Frosty's naming convention of "OkayToClose" with my own "DoNotClose".

The DoNotClose is declared in both the main module and in the userform as a public variable in general declarations as

Public DoNotClose As Boolean

thanks!

Candice

Notes: If I run subSave or subSaveAs from VBA editor the boolean value for DoNotClose changes from false to true. It does not matter if the document is saved or not, closed or not. Therefore the message is no longer displayed. And the way the code is currently written that is as it should be--the Save or SaveAs routine runs, the boolean value is saved as true, the message is no longer display if the user clicks on the red "X".

If the routines are triggered by clicking on the save or saveas command from the ribbon, etc., they run but the message is displayed as if the boolean value for DoNotClose is false.

If I try as suggested above and refer to the public variable in the userform SendAndSave two things must occur:

1. I have to declare DoNotClose as a public variable in the userform or I get "variable not defined."
2. The boolean value changes from false to true but not before the sub AutoClose runs, so that the message continues to appear whether the user has clicked on the red "X" or not.

[Frosty]

That's not going to work... because you have two variables when you want only one. I think I know what the problem is... you're using the ThisDoucment object, which is also, technically, a class.

1. Please please, decide on naming conventions.

Public DoNotClose As Boolean is not a good variable name. It is *definitely* a bad idea to have TWO variables with that name. So, first step:

Change both of those variables to "pub_blDoNotClose"

2. Scope/Lifetime concepts. This is the missing piece for why you can't see your DoNotClose variable (which you should rename) in your userform. Both ThisDocument and any userform are classes. That means that when you declare something as a public "variable" at the top of those code modules, they become "properties" of that class, rather than public variables accessible anywhere within your project.

3. Can you list the locations of your code in your project? I'm guessing it is just ThisDocument and your custom userform. Is that also where you put your AutoClose and other routines? Because I would suggest creating a new basic module (it should show up under the Modules folder in the project explorer), and then try that same Public declaration there-- then your userform and your other code can see it as a variable, rather than a public property of a class.

4. How you could make it work without creating a basic module... you could refer to your DoNotClose in your userform by referring to it explicitly -- ThisDocument.DoNotClose. However, there may be issues with that structure (although I can't think of any off the top of my head, I've also never attempted to add public properties to the ThisDocument object and utilize them).

[ac503683]

That's not going to work... because you have two variables when you want only one. I think I know what the problem is... you're using the ThisDoucment object, which is also, technically, a class.

1. Please please, decide on naming conventions.

Public DoNotClose As Boolean is not a good variable name. It is *definitely* a bad idea to have TWO variables with that name. So, first step:

Change both of those variables to "pub_blDoNotClose

Thanks, I will, but have not yet come up with a naming convention. I realize I have to clean the code up considerably.

I'm confused about the comment having two variables with the same name.
Currently I only had the one DoNotClose. I had tried declaring the variable only in the main document and using the variable in the userform, which gave me the "variable not defined". That was confusing enough so then I did declare in the userform as well. That made me more anxious (why would I need to declare the variable twice?)

Will work on this tomorrow and try again.

Thanks for the lesson--in defense, really I have spent quite a bit of time reading up on classes, variables, routines, procedures, projects, objects, commands, controls, yadda, yadda, since I started asking about this. I am as clueless as when I started. To this day, literally years later, I'm no more proficient than I was when I decided to try to learn VBA. I couldn't write a simple procedure from scratch if my life depended on it (thank goodness it does not!).

So, the folks here are truly lifesavers as far as I'm concerned. I thought I was fairly comfortable with some programming and code having used sql, fortran, etc., but VBA is beyond my learning capacity.

Regards and thanks again,
Candice

[Frosty]

You don't *have* to come up with a naming convention... you can just use the one I'm giving you ;)...

As for the two variables with the same name... in VBA, View > Project Explorer. That will show you your project in a tree view. You will at least see a folder named "Microsoft Word Objects" under which is something called ThisDocument. You *may* see one or more of the following folders: Modules(this would be all basic modules), Forms (any custom user forms you've created) and Class Modules (any custom classes).

Depending on the spot you put "Public DoNotClose As Boolean" -- that variable will have different scope, visibility and lifetime.

Consider the following code, contained in a basic code module:

Option Explicit
Public MyVar As Integer
Sub Test()
Dim MyVar As Integer
MyVar = MyVar + 1
MsgBox MyVar
End Sub
Sub Test2()
MyVar = MyVar + 1
MsgBox MyVar
End Sub

Run both of those macros multiple times... I think you'll see the difference. The difference is one of scope and lifetime. But they have the same name. This doesn't mean they have the same value, nor does it mean they are the same variable.

You never "declare a variable twice" -- VBA doesn't allow you to. It will give you an "ambiguous name" compilation error. If it does not give you that error, then you know what you've done is allowed... even though it can be confusing (i.e., the same variable name in different places).