Solved: Part 1, Close, Do Not Save Active Document With Userform

[ac503683]

Greetings all,

Big fan of VBA Express:hi:

Intro: Gov't worker in water quality trying to create a semi-automated process for review requests using a Word form with data captured in Access (for my database) but distributed to Excel (for those who ack!Access). The form will be filled out by various districts personnel in the agency located throughout the state and sent back via email. I'm not a VBA expert or programmer by profession. Rank amateur, green-as-grass. The form has potential for elegance (TY VBA!), but in my hands, I will be delighted to get the thing into production.

I've been "borrowing" code from several sites including Greg Maxey's but there are a few problems I can't seen to comprehend the issue to find a solution. So here I am. I have several questions all related to this project, but will start with this one.

OP is Windows XP with MSOffice 2007. The Word document is a macro-enabled template. The template has several form controls. The last one is a text form field that when the user moves out of the form field a userform automatically pops up giving the user several choices:

Save
Send as attachment
Cancel(return back to form)
Close and do not save

It is the last choice I have been banging my head on for a while. What I want to do is: close the userform, close the active document without saving.

I've tested some code separately and can get the userform to close OR get the active document to close without saving but NOT within the same procedure. It hangs up. I get the general run-time error '4198' command failed. I did get another error message once indicating that the word document could not close because a dialog box was still open. Makes sense...

Here is the code:


Private Sub cmdStop_Click()
If MsgBox(prompt:="Do you really want to close?", Buttons:=vbOKCancel) = vbOK Then
Unload frmSaveAndSend
Application.DisplayAlerts = False
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End If
End Sub


When debugging it highlights ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

I also tried calling a procedure before closing the document with no issues.

Thanks to all for your patience (as I'm afraid this is only the beginning).

Regards!

[Frosty]

Hmm... is the cmdStop_Click called from within frmSaveAndSend, because that's not necessarily going to work.

Think about what you're trying to do...

1. You've got code in a macro-enabled document.
2. You've got a user form in that macro-enabled document, which is trying both to unload itself, and close the document that holds the code.

It's like you're trying to do one of those cartoon things where the cartoon character climbs down a rope, gets to the end of the rope, needs more rope... and so cuts the rope above him.

Sometimes VBA will save you from yourself by holding things in memory when, technically, they shouldn't be held in memory (using a macro-enabled document to close itself). But I suspect you've reached the limits of VBA saving you from yourself when you're trying to do this where you're trying to do it.

Can you post the macro-enabled document? I suspect you need to do something like the following in a regular module... and that this is the main routine which invokes and shows the userform (also, make sure to remove the Unload statement from within the form code):


Sub Main
Dim f as frmSaveAndSend

Set f = New frmSaveAndSend
f.Show
'then the form does whatever it wants to do... and when control is passed back to the calling macro, unload the form
Unload f
'now mark your document as saved (you *did* save it elsewhere? Or do you never want to save it?)
ActiveDocument.Saved = True
'and close it
ActiveDocument.Close
End Sub

[Frosty]

Sorry, I see it's a macro enabled template... which is better, but the same kind of stuff still applies. The only difference is that, in normal behavior, a macro-enabled document is unloaded from memory when it is closed. A macro-enabled TEMPLATE is unloaded from memory when the last open document based on that template is closed.

So if you opened 3 documents based on your macro template, then you would hold it open until the 3rd document was closed.

[ac503683]

Last response first: Yes, it is a macro enabled template. I'm now wondering if it should be distributed as a macro enabled document? I originally thought a template for distribution purposes.

There is a procedure that removes the orignal template and attaches the normal.dotm simply to break the relationship and remove the macros from the saved document. It works, but it is in the beta phase with no decision on need or how to implement. I really don't want the user to mistake the saved document as the template. This forces the user to go back to the template as the template connects to a database table in order to fill some of the form fields. And I didn't want the saved document to maintain connection with the database where the input values could be arbitrarily changed by a different user.



It's like you're trying to do one of those cartoon things where the cartoon character climbs down a rope, gets to the end of the rope, needs more rope... and so cuts the rope above him.

Sometimes VBA will save you from yourself by holding things in memory when, technically, they shouldn't be held in memory (using a macro-enabled document to close itself). But I suspect you've reached the limits of VBA saving you from yourself when you're trying to do this where you're trying to do it.



Makes sense now that you have pointed out the obvious. In all my searches, I think it is assumed the reader understands this, but it's never been really all that clear when to use the main routine and when it can be put in the userform. I had erroneously assumed that once the userform is activated then all routines had to be in the userform until it was closed...lots to learn...now I know why I'm having other problems. I've read about creating a boolean routine but couldn't quite figure how that worked within the userform. (duh!)

Here is the code for the document:

When a new document is created, it calls up an intro userform that explains how to fill in the form fields. Proceeds through a database query that fills a drop-down list then finally ends up where the user decides if they want to save it, email it, return to the document or trash it without saving--the frmSaveAndSend


Option Explicit

Private Sub HowToUse()
Dim oFrm As frmIntroduction
Set oFrm = frmIntroduction
oFrm.Show
Unload oFrm
Set oFrm = Nothing
End Sub

Private Sub Document_New()
Call HowToUse
On Error GoTo Document_Open_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=NamedDataSouce.mdb"
rst.Open "SELECT DISTINCT TOP 25 [Field] FROM tblNamedTable ORDER BY [Field];", _
cnn, adOpenStatic
rst.MoveFirst
With ActiveDocument.FormFields("Field").DropDown.ListEntries
.Clear
Do
.Add rst![Field]
rst.MoveNext
Loop Until rst.EOF
End With
Document_Open_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
Document_Open_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume Document_Open_Exit
End Sub

Sub CascadeList()
More database stuff
End Sub

Sub CascadeList1()
More database stuff
End Sub

Sub PermitFill()
More database stuff
End Sub

Sub OpenCalendar()
frmCalendar.Show
End Sub

Sub OpenSaveAndSend()
frmSaveAndSend.Show
End Sub


Thanks Frosty! Your comments bring clarity to this befuddled mind.

Candice

[Frosty]

Candice,

Couple of comments on the above.

1. I think I'd stick with a macro-template rather than a macro-document. That way you have a central location for your code. The last thing you want is users doing a "Save As" of an existing saved macro document, and then spending time why they are having errors connecting to your updated database... only to find out that they are using some older version of the code.

2. As I said before-- a macro template is held in memory until the last document "attached" to it (as in, the :AttachedTemplate: property) is closed. There are exceptions to this. You've listed one-- if you changed the .AttachedTemplate property, that is, to the macro-template, a "release" -- and it should essentially disappear. But this process has always been a little wonky, and may or may not work (generally, the "inelegant" ways of dismissing documents don't allow Word to do its typical "garbage collection" (a concept in which the application re-claims unused portions of memory/RAM it had previously said it needed).

3. With #2 in mind, then, there is a third option -- the Global Addin concept. This is a macro-template which would be located in the Startup directory (whether the default, or a custom path you set in the Word Options > File Locations area). You would need to modify your code to use this as an Addin, and that also raises the issue of how you deploy this addin to multiple people. In general, a file is "in use" whenever *someone* is accessing it. A macro template on the network could be held "in use" even when all of your end-users insist they don't have it open (or a document based on it open). There are a lot of ways to approach this issue, but I'm just trying to give a surface view of the "distribution" issue.

In general, there are multiple problems to solve here: your development process (making it do what you want it to do, and not break) and your deployment process (getting it to the end-users) and your update process (putting development and re-deployment together).

* - With a macro-enabled document-- you have deployment issues, because you've got multiple versions of the document everywhere.
* - With a macro-enabled template on which the documents are based-- you may have re-deployment issues if each end-user isn't using their own copy of the template, but rather a central copy of the template. You can have a central copy of the template on the network, but you want to make sure it's a read-only version, so no one can "lock" it from being updated by you (which is only solved, generally, by making everyone restart Word).
* - With a global adding, you have the same issues as a macro-enabled template, in terms of deployment, but less issues in development when wanting to do things like "close the document without saving changes" -- because your macros don't get released from memory until Word actually closes (Application.Quit is a lot different from Document.Close -- but not to a macro-template, because that *is* the application, as far as its concerned).

4. Code placement, when to put in a userform and when not. The reason people don't say "this is the way to do it" is because there are differing opinions, even by knowledgeable programmers. I am in the camp of preferring to have my userforms *only* have code related to the display/capture of information from the end-user. All code related to "doing" stuff to a resultant document (or an existing document) I leave outside of a userform. But this is only one approach.

5. Approaches to accessing a userform:
couple of different ways to do this. I prefer to create a new instance of a userform via this
Dim f As frmMyUserForm
Set f = New frmMyUserForm
f.Show

However, you can just as easily do...
frmMyUserForm.Show

Both ways will show the userform-- the difference is that my way will show a "clean" version of the userform (that's the "New" keyword), rather than something which might be floating around in memory from a previous use of it.

6. The immediate solution...
I think all you have to do with the code you posted is the following-- remove the Unload statement (and perhaps replace with a Me.Hide) from the code with frmSaveAndSend.

And then replace your existing routine with this:


Sub OpenSaveAndSend()
frmSaveAndSend.Show
On Error Resume Next
Unload frmSaveAndSend
On Error GoTo 0
ActiveDocument.Saved = True
ActiveDocument.Close
End Sub


However, if you need to deal with options on whether to actually perform the close without saving changes (I'm old school, and prefer it to be two lines of code, rather than a parameter to the close event), then you'll need to check any controls on your form (did they check a box to indicate wanting to close without saving changes?) to see whether you want to do that or not.

In some cases, you may also get a prompt about saving the actual macro-template (depending on how things are coded). If you need to make sure the end-user doesn't get that prompt, you can also try using a ThisDocument.Saved = True somewhere before the .Close method. However, be careful having that code in place while developing-- it could result in you losing changes to your actual macro-template.

Hope this helps!

Jason aka Frosty

[Frosty]

There are a few other implicit questions in your post which I didn't answer-- creating a "boolean routine within your userform" -- you may need to restate your question. But I *think* the quick answer to this is

A Subroutine (Sub OpenSaveAndSend) *does* stuff.
A Function *does* stuff, but also *returns* stuff, a la...

Function fAmISaved() As Boolean
fAmISaved = ThisDocument.Saved
End Function

That will return "True" if the document/template containing that macro is currently saved, or "False" if it isn't.

However, sometimes you might not be able to call functions/subroutines from other places in the document. This is related to Public/Private keywords, as well as concepts called "Scope" and "Visibility." That may give you enough to go on, in terms of searching on those terms and how they might be hindering what you're trying to do.

[Frosty]

Also-- you don't need to remove the attached template of a macro enabled template in order to have a document based on it not have macros. "Children" of macro templates do not have code in them, unless something wonky is going on. And since you are using the Document_New event and having your code work (as opposed to Document_Open), I suspect this would be true for you.

[ac503683]

Thanks Frosty,

I'll comb through your comments in greater detail as I can tomorrow and provide feedback.

You've hit the tip of the iceberg. Distribution is a key issue.

I had given consideration of having the template reside only on a networked drive on one of our servers, but haven't yet delved into how to secure it from dabblers or how to make is accessible to more than one user at a time...WHEW! I'm also concerned about the database and the macros. Not that it would cause any catastrophe, but it require more maintenance time than it is worth should there be more than one versioned database table flittering about. {shudder over the thought}

Personally I would prefer that the template and its associates reside globally rather than locally for QA/QC purposes. Easier to maintain and cleanup one rather than many. How to secure it is beyond my scope and focus currently since I'm still pondering simple functionality. Heh!

As to your last comment, I thought the same. Again, I assumed that the children of the template would not have the macros because of the Document_New event. I will have to look at this afresh. It does begin to spin a bit after a while.

Regards,
Candice

[ac503683]

Still working on this case...had a minor setback due to work obligations.

In response to the "children" documents inheriting the macro when saved: No they do not. However, what does happen is the macro populates the drop-down fields based on decisions (choices) made when the macro is running. So each document, once saved, will have a list of items in the drop-down fields that are no longer "linked" to the database and therefore, do not provide any logic if the user changes the original choice. The user can still choose a different item in the list but it doesn't affect the outcome of the succeeding drop-down list (as it should if the macro was inherited).

This makes for confusion for the user because it appears the database relationships still exist.

Since I'm changing the protection of the document from a form that can be filled to a "read-only" once saved. I shouldn't really need to change the template attached to the document. I'll see how that works.

[Frosty]

You might want to utilize both Document_Open and Document_New events then, and then run that validation routine both when creating a new document and when opening an existing document. As long as the document is still attached to the parent template, you still have access to the code (and thus, a relationship to the database).

But if you have offline usage scenarios, the typical way to handle this is to not only have your parent template be available when offline, but have a "data dump" process when Word is launched, via a global addin, and then utilize that local copy of the data for your purposes.

General overview and broad strokes, obviously-- but there are a lot of options, it really depends on how you want it to work and in what scenarios, at the end of the day.

[ac503683]

:thumb Frosty,
The code you gave me works, but I don't understand why it works.

I've had to borrow a laptop with Windows 7 and am now getting all types of compile errors, etc. What had "worked" now doesn't in some areas and I'm at a loss as to why it does or does not work. Since my agency will eventually migrate from XP to 7.0, I've got to get the fundamentals down pat.

Thanks again! I am truly grateful! Problem solved. Haven't given up--just need to take a step back again and figure out the fundmentals. I'm always humbled by the knowledge shared here.

I've got VBA for Dummies (meh!) and Walkenbach's book for Excel 2007 VBA Programming. Any suggestions on other resources?

[Frosty]

Nothing you've displayed would indicate why there are compile errors if you're using the same code template between versions. Word is pretty good at being backwards compatible, in terms of compilation.

However, if you've simply imported the code from a .dot in to a .dotm, then you're probably going to want to look at the Tools > References in your original project (you can open a .dot in Word 2010), and verify those same references exist in your new template (the ADODB objects would require a special reference, not included in a new macro template).

I don't have any suggestions on books, honestly. I haven't used one for a long time, but I imagine they all cover the basics. The advanced stuff, I suspect the internet will be better than any book.

In terms of general approaches:
1. Make sure you always use Option Explicit in every module (you can turn this on by having "Require Variable Declaration" checked on in Tools > Options > Editor within VBA.
2. Explore and make use of the Immediate Window, the Locals Window and the Watch window. Those are each good ways of figuring out why code works (much better than trying to put message boxes in your code to display values of variables you're curious about)
3. Avoid using public and private variables until you're really familiar with the concept of scope. Dimming variables in each and every sub routine, as well as passing parameters will seem like it takes you longer-- but it will make you a better programmer. And it will also make it easier to figure out what's going on in your code (especially the Locals window).

And... come back. There's a lot to learn, and I think these kinds of forums are good places to do it.

Best!
Jason aka Frosty

[Frosty]

Oh, and in terms of the more recent versions of Word... the default security on macro projects will generally disable all of your macros. You'll want to explore the Trusted Locations and Macro Security areas when you're developing to make sure your code will actually be accessible.

[ac503683]

Nothing you've displayed would indicate why there are compile errors if you're using the same code template between versions. Word is pretty good at being backwards compatible, in terms of compilation.

Jason aka Frosty

Yeah, it perplexed me as well so I took it as a sign to call it a day.

After posting I had other unrelated issues more to do with working on a borrowed pc than anything else. Couldn't duplicate the errors this morning so I'll just chalk it up to the server gods and move on. Today all is well. I had changed the securities, so can only wildly guess what was going on. It went wonky.

On another note, accessing the userform using your suggestion:

Dim f as frmMyUserForm
Set f = New frmMyUserform
f. show

somehow negates the cancel command:



Private Sub cmdCancel_Click()
Unload
frmSaveAndSend
Set frmSaveAndSend = Nothing
End Sub


removing "New" in the code brings it back into action.

So, I'm wondering if there is something in the properties in the command buttons of my userform that I need to change. Love the elegance of "clean code" and would like to follow best practices.

Regards,
Candice

P.S. I'll be a frequent visitor, as I would really like to see this project survive and take flight. Oh, and I've some crude Excel stuff that works but would like the little darlings go into production as well.

[ac503683]

Back to the drawing board:
I'm still getting the following error:

Run-time error '4198': Command failed for the same userform as above for the same command and the same line is highlighted when debugging. The active document will not close.

The code in the user form:



Option Explicit


Private Sub cmdCancel_Click()
Unload
frmSaveAndSend
Set frmSaveAndSend = Nothing
End Sub


Private Sub CmdStop_Click()
If MsgBox(prompt:="Do you really
want to close?", Buttons:=vbOKCancel) = vbOK
Then
Application.DisplayAlerts =
False
ActiveDocument.Close
SaveChanges:=wdDoNotSaveChanges
End If
End
Sub


Private Sub cmdSave_Click()
Dim aStory As
Range
Dim aField As Field
With
Dialogs(wdDialogFileSaveAs)
If .Show = 0 Then Exit
Sub
End With
For Each aStory In
ActiveDocument.StoryRanges
For Each aField In
aStory.Fields

aField.Update
Next aField
Next
aStory
ActiveDocument.Save
End Sub


Private Sub cmdSend_Click()

ActiveDocument.SendMail
End Sub


The subroutine:



Sub OpenSaveAndSend()
Dim s As
frmSaveAndSend
Set s =
frmSaveAndSend
s.Show
On Error
Resume Next
Unload frmSaveAndSend
On
Error GoTo 0
ActiveDocument.Saved =
True
ActiveDocument.Close
End Sub


Note: I tried unloading the document using "unload s" but that didn't work.

Sorry to keep repeating this. I seem to running in a perpetual circle.

Thanks,
Candice

[Frosty]

You've got strange line breaks in your code... but since it wouldn't compile at all, I'm assuming that's a vagary of the board.

Couple of concepts here...

UserForms are a class/object. What this means is that when you create them, you are creating an "instance" of that class. You can have multiple instances, much like you can have multiple string variables (as long as they are different names).

So when you dimension ("dim") a class, all you are doing is allocating memory space. You are not actually creating the object. This is what the "New" keyword does.


Sub OpenSaveAndSend
'allocates a block of memory for your userform
Dim f As frmSaveAndSend

'Creates a new/fresh instance of that userform (this also triggers the Initialize "event" of the form)
Set f = New frmSaveAndSend

'display the form (also triggers the "activate" event of the form)
f.Show
'at this point, execution of this routine is halted... you will not come back to
'this line of code until the form is no longer being displayed

'this will unload the form from memory
Unload f

'this marks your document as saved
ActiveDocument.Saved = True
'this closes the document
ActiveDocument.Close
End Sub

Now... within your form, don't use the Unload command. Just use the Me.Hide command. This serves to hide the form and allow your OpenSaveAndSend subroutine to continue processing (which will take care of the unloading of the form). Your cancel routine should just look like this:

Private Sub cmdCancel_Click()
Me.Hide
End Sub


If you unload the form from within itself... then you will also continue the execution of the OpenSaveAndSend subroutine... but there is no form to unload, unless VBA first loads it.

There's an additional piece of information here, which is VBA treats UserForms a little bit differently than other classes -- it allows you to refer directly to the name of the form, rather than requiring you to always create a variable referring to the class. To me, this is facilitating "lazy" coding (others will disagree), but mostly what it does is facilitate a lack of understanding of what is going on behind the scenes, in much the way that not using Option Explicit still allows you to make working code, but it can be a bear to debug...

So, I would recommend not unloading your form from within your form. Use Me.Hide instead, to allow the calling code to continue processing.
Set explicit instances of your form from the calling routine (for you, OpenSaveAndSend).
Don't ever refer to the form by name (even though VBA allows you to do this)
Take out your error trapping, so you can understand why an error would be caused.
Identify what you actually want to have happen, and when. If you are using your cmdStop to close the active document, but then also have your calling code closing the activedocument... you are going to be closing two documents (or generating an error, if you only have one document open).

[Frosty]

Additional notes:
Even though VBA will allow the following code, I don't think it helps you learn what is happening.

Sub Demo
frmSaveAndSend.Show
End Sub

And then within the userform, you have code which does this

Private Sub cmdCancel_Click()
Unload frmSaveAndSend
End Sub

What is *technically* happening up there would be fine, since there are no references to frmSaveAndSend after unloading it.

But as soon as you do something like this....

Sub Demo
frmSaveAndSend.Show
Unload frmSaveAndSend

You will see that VBA will have to do all of the following events of the form...
1. If the form is not already loaded in memory (which you can check by checking UserForms.Count, and then checking the .Name property of each one), it will Load the form, which causes the Initialize event.
2. The .Show command then triggers the Activate event of the form
3. Code execution in the calling routine is paused (assuming the form is being shown modally-- another caveat to the above info), until the form is hidden somehow (whether by Unloading the form from within itself, or hiding the form from view)
4. Assuming you unloaded the form... it's now no longer in memory, but you have a line of code which says "Unload frmSaveAndSend" ... so what does VBA do? It has to re-initialize the form solely for the purpose of unloading it. Which is what it does.

You can see this behavior by creating a form which has a single button on it, called cmdOK, and the the code of the form should have the following:

Option Explicit
Private Sub UserForm_Initialize
Debug.Print "Initialize the form"
End Sub
Private Sub UserForm_Activate
Debug.Print "Activate the form"
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Debug.Print "closing the form"
End Sub
Private Sub cmdOK_Click
Debug.Print "Clicked OK"
Unload frmMyForm
End Sub

Then have your calling routine like this:

Sub Demo
frmMyForm.Show
Unload frmMyForm
End Sub

Then step through all of the code...and watch what happens... if you want to add & UserForms.Count to the debug.print commands, you'll see how things are getting a little screwy and confusing.

I think you'll be able to see that using Me.Hide will be a more effective way of troubleshooting issues... unless you have a need to set your .ShowModal property of the userform to False. That would be a different conversation, as you have to handle non-modal forms differently (you actually need to handle unloading the form from within the form itself). But Modeless userforms are a lot less common, in practice, so I would cross that bridge when you come to it. It will be easier to "break the rules" when you have a deeper understanding of them in the first place :)

[Frosty]

In fact, just to continue a little bit of the lesson (and refresh my own understanding), you have the following "primary" events in a userform during the process of showing and closing the form:
Initialize = when the form is loaded into memory
Activate = when the form is actually displayed
QueryClose = the form has been instructed to be unloaded
Terminate = the form is about to be Unloaded

So there are a couple of different ways to handle the normal...

UserForm1.Show
Unload UserForm1

is technically all you need in your calling routine, as long as your "cancel" button simply uses Me.Hide.

You only need
UserForm1.Show in your calling routine, if you're going to put Unload UserForm1 in the "cancel" button of your userform...HOWEVER, assuming you use Me.Hide in the userform during the cancel button, you will avoid triggering the QueryClose event if you instead use this:

UserForm1.Show
Set UserForm1 = Nothing

BUT... avoiding the QueryClose event is not a good thing, because it is actually triggered by hitting the Red X on the userform and when you use Unload (although the CloseMode parameter will be different) -- so you can effectively disable the Red X, but you can't make it go away. For me, you should always try to design userforms so that they work the same way as built in dialogs, which means:
1. The Cancel Button is triggered by hitting the ESC key (that means you set the .Cancel property to True and the .Caption button to "Cancel" for the command button you've designated as your Cancel button). I typically name this control cmdCancel
2. You put Me.Hide in the cmdCancel_Click event.
3. You have the QueryClose event call the cmdCancel_Click routine if the CloseMode = 0 (i.e., when the Red X is pressed)

This gives you a uniform approach to handling your form after the end-user has dismissed... and then you always have the same situation to deal with in your calling code: the userform has been hidden but is still in memory.

In addition, you can continue to use the userform (and the values therein) while your calling routine is running (so you can continue to check the vales chosen by the user) until you actually choose to unload the form.

If you refer to your forms specifically by the name of the user form, you can't know for sure whether the form has been unloaded or not... because it will always trigger a new instance of the form rather than an error.

So my standard "best practice" for form code is the following code in a module

Sub DemoHandlingAUserForm
Dim f as frmMyUserForm

'initialize the form
Set f = New frmMyUserForm
'show the form
f.Show

'check my custom public property of the form, to see if the user cancelled in some way
If f.blCancel = True Then
GoTo l_exit
End If

'check values chosen in the form...
With f
If .chkMyCheckBox.Value = True Then
'do something, because the user clicked this checkbox, etc etc
End If
End With

l_exit:
Unload f
End Sub

And then, in the form, I have *at least* the following events set up, assuming the cmdCancel button has the .Cancel property set to True.

Option Explicit
Public blCancel As Boolean
Private Sub cmdCancel_Click
blCancel = True
Me.Hide
End Sub
Private Sub UserForm_QueryClose (Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
cmdCancel_Click
End If
End Sub
Private Sub UserForm_Initialize
'anything I need to initialize here
End Sub

Hope this helps your understanding. Don't forget to use Debug.Print "this event here" in various events, and show your Immediate Window to help yourself learn about the various events. UserForms can be really tricky- and trying to figure out what is firing when can cause a lot of headaches.

If you don't follow the above, then you easily create the scenario where the end-user clicks the red X, the form is unloaded... but then you start checking values of the form in your later code... which causes a new instance of the form to be created with default values. So instead of an error, you get an operation which might be the exact opposite of what the end-user expected.

There are other ways to approach this, but this is my preferred practice.

[ac503683]

Thanks again! I'll take this home tonight and try again. Quickly: the line breaks (?) no clue as the code is not displayed like this in the original code nor can find anything underlying to force a line break. Please note that I did a quick copy/paste to get this posted before trotting off.

Again, completely perplexed as to why what worked initially is now buggy. I usually backup a working copy and when all goes up in flames simply trash the beta, open the working template, test, rename and move forward again.

Really I appreciate this; a tad concerned as I considered the issue to be rather simple and more of an indication of developer ignorance that when once pointed out the fundamentals of code, it could be corrected easy enough. (I have much more complicated issues yet to discuss with this project). One step at a time....

Have a good evening and will report back in the morn.

Candice

[Frosty]

Actually, almost all problems are simple... when you already know the answer (or at least one of the possible answers) ;)

Your main problem with this is that you didn't know any of the answers, and so had aggregated a number of different answers/approaches, which left things confusing.

I'm only posting what has worked for me and my philosophical approach to coding (separating "data" from "doings" -- which, when applied to form design, makes me take a lot of code out of dialogs which "does stuff" preferring to leave only code in a userform which relates to the gathering of information from the end-user).

But there are valid approaches to having all of your code in a userform... and very very little outside of it. I just don't happen to organize my code that way. I'm actually hoping someone else will weigh in with a contrasting opinion -- because that opinion may feel more intuitive to you, and thus easier to learn.

[ac503683]

Thanks all for your time and patience.

First, some of my previous posts have indicated varying degrees of success and misinformation, of which, Frosty has held my hand through it all.

Today, I had the time to meticulously work through some the tutorial matter and tested some of the code. As a confused newbie, this has helped tremendously.

Here is what I have now in the document:


Sub OpenSaveAndSend()
'code below for testing
Dim sFrm As frmSaveAndSend
Set sFrm = New frmSaveAndSend
sFrm.Show
On Error Resume Next
Unload sFrm
Set sFrm = Nothing
On Error GoTo 0
ActiveDocument.Saved = True
'ActiveDocument.Close
End Sub


In the userform SaveAndSend:


Private Sub cmdCancel_Click()
Me.Hide
End Sub


Private Sub cmdStop_Click()
If MsgBox(prompt:="Do you really want to close?", Buttons:=vbOKCancel) = vbOK
Then
Me.Hide
Application.DisplayAlerts = False
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End If
End Sub

I stated incorrectly that 'New' was causing issues. No it does not.
I changed some of the code based on Frosty's recommendations. :bow: Nice!

After testing each line I've been able to replicate the same results/issues below:

In subSaveAndClose the ActiveDocument.Close is a comment line at this point. For some reason when this line is part of the code, when the cmdCancel_Click() is activated the routine tries to run through the subSaveandClose. I then get a "Command Fail" error at ActiveDocument.Close

In cmdStop_Click I get the '4198 command failed' error message as previously stated.

About the best I can do is grasp that the macro hangs up because some other window is still active and open preventing the document from closing. I'm guessing that the cancel command brings it back to the subSaveandSend where it continues to try to close the active document but can't. At least that continues to be consistent.

There is ONE odd instance that works: When I removed some of the code, saved the template, closed the template, reopened the template, put the code back in, saved it, it worked ONE time. If I test the code again without closing the template, it hangs up. If I close the template, reopen the template, it hangs up. I have repeated this process, so it seems "something" remains open in the background causing this.

I must confess I have tried so many methods/variations of coding in the userform, out of the userform,(considered and tried both) calling a subroutine, not calling a subroutine, searching the internet for examples, explanations, stealing code, trashing code, making up code, etc., that I no longer trust myself to comprehend any of it. It's been on/off a year in trying to get this to work as time permits. Right now I can dedicate a few more days and then will have to set it aside again. This project is going into the NFA file as a hobby past time for me as, honestly, the answer escapes me. If I can't grasp this, well, forget the more challenging issues I've got.

I think at this point, I'll distribute the form as a text box fill-ins and leave it for manual input and manual process. The deadline to put the form in production has passed and I have little time left to automate the process.

The Excel macros aren't as demanding and don't need such a strict protocol where the user can customize the workbook, tailor the macros and make refinements as needed. They are more a tool than a process where this form must adhere to a protocol originating from my work place and protect both the template and database linked to it.

Thanks all! If I ever get it to work I'll post it here.

Candice

[Frosty]

I don't know why you have two different places that do ActiveDocument.close. That means that at least in some circumstances, you are going to be closing two documents) or attempting to. Without seeing more code, I can't tell if this is a problem, but I suspect it is.

Do you *always* want to close the active document after you're done with your form, regardless of what the end-user has selected/done while the form was displayed? If so, then the .Close code should be in the calling routine. If not always, then you can either close in the userform or close in the calling routine *before* you unload the form, based on whatever control(s) value(s) would indicate a desire to close the form (even if you just create a Public blCloseDocument As Boolean at the top of the form, and then set it to true if the end user selects OK in your msgbox)

I wouldn't beat yourself up. This stuff can be hard. It takes time. And if you don't give yourself more than very little chunks of time widely spaced apart, it can be nearly impossible to progress.

Teachers complain about the length of summer breaks, because they spend the first month or two reminding kids the lessons from last year. Imagine how difficult it would be if summer breaks were 10 months long! You'd never really learn anything ;)

Good luck!

[ac503683]

Progress!! (sort of, but I'll take it!)

As reported above there is ONE instance that the code works. I had to scratch the itch...because I was testing both from VBA editor and the Word document.

As long as the macro is run from the VBA editor everything runs smoothly:
The SendAndSave userform cancel command cancels and closes the document. The stop command does the same. No glitch, no code hidden as a comment line, etc., so, the code you gave me Frosty is in full operation. It does not matter if the document is protected or not. What I did was simply run the macro independently, tested the command buttons, and all worked as intended.

The problem is linked to how the macro runs from the document form.
The document is protected so that only the form fields can be navigated to and filled in. The user can either point and click or tab to the next field.
The last field is a text box comment field. When the user exits out of the field the macro OpenSaveAndSend runs. At this point if the user chooses "Cancel" or "Close & Do Not Save" that I get the command failure message.

A couple of things come to mind:

1. Could it be one of the other userforms are causing the problem? The are two userforms that run before the SaveAndSend. One is an information userform that supposedly follows Frosty's coding recommendations. The other is a calendar, which does not.
2. Is it due to a still active connection to the database? Perhaps the connection should be closed earlier? This is an example of the code to access the database table records:



Private Sub Document_New()
Call
HowToUse
On Error GoTo
Document_Open_Err
Dim cnn As New
ADODB.Connection
Dim rst As New
ADODB.Recordset
cnn.Open
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=MyDatabaseTable"
rst.Open "SELECT DISTINCT TOP 25 [Field] FROM MyTable ORDER BY [Field];", _
cnn, adOpenStatic
rst.MoveFirst
With ActiveDocument.FormFields("Field").DropDown.ListEntries
.Clear
Do
.Add rst![Field]
rst.MoveNext
Loop Until rst.EOF
End With
Document_Open_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
Document_Open_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume Document_Open_Exit
End Sub


Edit to add: It does not matter how the user navigates (in the document) to the comment field where OpenSaveAndSend runs. The command failed error message always comes up.

[Frosty]

I don't think it is either of those things. You adodb connection code looks fine, apart from whatever is causing extra returns.

But can't you step through or put a breakpoint? You don't have to run the code from the VBA editor to use break points.

Couple questions:
If you have the template open in word, and do everything else from word (enter the last form field to trigger save and send, etc... Does it run? If the template isn't open, that's when it fails? What if you run the save and send macro in either of the above two scenarios by calling from macros list (not VBA editor)?

[ac503683]

Grettings all,

Janson (aka Frosty), a BIG THANK YOU! I have printed this topic for reference and saved it electronically for future endeavors. Write a book!!! Seriously, without your detailed analysis and tutoring I would still be lost.

Report: After discovering that the issue originally posted was due to trying to tigger the macro through a text form field, I deleted that particular action. So the text field now does not trigger the macro on exit or entry.
Once done, all that was recommended by Frosty worked. Ulitmately my original design was an awkward attempt to control how the user saved and closed the template-based document.

Here is what I have done instead:
1. I found some code to prevent the user from closing or saving the document by typical means.
2. When the user tries to close or save the document as in #1 the macro "SaveAndSend" is triggered showing the associated userform with the available choices.
3. I modified Frosty's code slightly so that when the user chooses the cancel out, the userform simply closes returning the user back to the form.

#3 above has exposed another challenge, but I will leave that for another topic. Admittedly, the code submitted here may not follow best practices, but it is a work-in-progress, and as my knowledge expands I intend to refine the code.

The subroutines in the main document:


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

Sub FileSaveAs()
If MsgBox(prompt:="Are you ready to save and close document?", Buttons:=vbYesNo) = vbYes Then
Call OpenSaveAndSend
End If
End Sub
Private Sub OpenSaveAndSend()
Dim sFrm As frmSaveAndSend
Set sFrm = New frmSaveAndSend
sFrm.Show
On Error Resume Next
Unload sFrm
Set sFrm = Nothing
On Error GoTo 0
End Sub

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

The userform SaveAndSend:
Option Explicit
Private Sub cmdCancel_Click()
Me.Hide
End Sub
Private Sub cmdStop_Click()
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
Private Sub cmdSave_Click()
Dim aStory As Range
Dim aField As Field
With Dialogs(wdDialogFileSaveAs)
If .Show = 0 Then Exit Sub
End With
For Each aStory In ActiveDocument.StoryRanges
For Each aField In aStory.Fields
aField.Update
Next aField
Next aStory
ActiveDocument.Save
Me.Hide
End Sub
Private Sub cmdClose_Click()
Dim aStory As Range
Dim aField As Field
With Dialogs(wdDialogFileSaveAs)
If .Show = 0 Then Exit Sub
End With
For Each aStory In ActiveDocument.StoryRanges
For Each aField In aStory.Fields
aField.Update
Next aField
Next aStory
ActiveDocument.Save
Me.Hide
Application.DisplayAlerts = False
ActiveDocument.Close
End Sub
Private Sub cmdSend_Click()
Dim aStory As Range
Dim aField As Field
With Dialogs(wdDialogFileSaveAs)
If .Show = 0 Then Exit Sub
End With
For Each aStory In ActiveDocument.StoryRanges
For Each aField In aStory.Fields
aField.Update
Next aField
Next aStory
ActiveDocument.Save
ActiveDocument.SendMail
End Sub
Private Sub UserForm_QueryClose _
(Cancel As Integer, CloseMode As Integer)
' Prevents use of the Close button
If CloseMode = vbFormControlMenu Then
MsgBox "Clicking the Close button does not work."
Cancel = True
End If
End Sub


Thanks to this forum and articles! When I work in VBA I use this source frequently.

Edit to add I could remove some of the redundant code and call the code when the specified command button is clicked in the userform.