Stopping document-close? (WinNT/Word97)

Topic

New Reply

I have a template which produces documents that are updated by the users. I need to have it check a cell in a specific table and match it to the relevant document property when they have edited the document, and I want to do this when they hit File-Close (a lot of our users close the document and say ‘yes’ to ‘save changes?’ rather than saving it first then closing it). The problem occurs if the table cell and property *don’t* match.

If that’s the case, I need to stop the file-close, tell them to buck their ideas up and let them go back in to correct the table cell – but Exit Sub doesn’t seem to do it! Is there a special way to stop the document closing on request?

Many thanks in advance!

Reply | Quote

Replies

You can use the application-level DocumentBeforeClose event for this; it has a Cancel argument you can set to True to prevent closing the document. You need:

1. A class module, say clsEvents, in which you declare a variable of type Application that will handle events:

Public WithEvents app As Application

Private Sub app_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
If MsgBox(“Are you sure”, vbQuestion + vbYesNo) = vbNo Then
Cancel = True
End If
End Sub

In your template, you would replace the message box by a test that compares the specific cell to a document variable.

2. A standard module that declares an instance of the class module:

Public myEvents As New clsEvents

3. Code in the Document_New and Document_Open events of ThisDocument to initialize myEvents:

Private Sub Document_New()
Set myEvents.app = Application
End Sub

Private Sub Document_Open()
Set myEvents.app = Application
End Sub

Note: because this is an application-level event, it will fire for ALL documents, not just those based on your template. So you will want to test if the document is attached to your template:

If ActiveDocument.AttachedTemplate = “MyTemplate.dot” Then
…
End If

I have attached a zipped template with the application level event handler described above.

Reply | Quote

You can use the application-level DocumentBeforeClose event for this; it has a Cancel argument you can set to True to prevent closing the document. You need:

1. A class module, say clsEvents, in which you declare a variable of type Application that will handle events:

Public WithEvents app As Application

Private Sub app_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
If MsgBox(“Are you sure”, vbQuestion + vbYesNo) = vbNo Then
Cancel = True
End If
End Sub

In your template, you would replace the message box by a test that compares the specific cell to a document variable.

2. A standard module that declares an instance of the class module:

Public myEvents As New clsEvents

3. Code in the Document_New and Document_Open events of ThisDocument to initialize myEvents:

Private Sub Document_New()
Set myEvents.app = Application
End Sub

Private Sub Document_Open()
Set myEvents.app = Application
End Sub

Note: because this is an application-level event, it will fire for ALL documents, not just those based on your template. So you will want to test if the document is attached to your template:

If ActiveDocument.AttachedTemplate = “MyTemplate.dot” Then
…
End If

I have attached a zipped template with the application level event handler described above.

Reply | Quote

Thanks again, Hans – it’s beginning to feel like posting a question in the lounge is asking you the question direct – it always seems to be you that answers! I must admit I wish I knew as much about Office, VBA etc as you seem to …

This application level event business is new to me, so I’ll have to go carefully and make sure I read all your instructions properly, but I’ll let you know how I get on …

Again, many thanks

Reply | Quote

Thanks again, Hans – it’s beginning to feel like posting a question in the lounge is asking you the question direct – it always seems to be you that answers! I must admit I wish I knew as much about Office, VBA etc as you seem to …

This application level event business is new to me, so I’ll have to go carefully and make sure I read all your instructions properly, but I’ll let you know how I get on …

Again, many thanks

Reply | Quote

Right, I think I’ve got all of it except one little bit – the actual code I want to run before the document closes – where do I put this? Is it under the sub app_documentbeforeclose bit?

Muchos gracias!

Reply | Quote

Yes, you’d write the code in app_DocumentBeforeClose. Depending on the result of the test, set Cancel to True if you want to keep the document open. Something like:

Private Sub app_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
Dim strCell As String
Dim strVar As String
‘ Text of cell
strCell = ActiveDocument.Tables(1).Cell(2, 3).Text
‘ Get rid of cell marker
strCell = Left(strCell, Len(strCell) – 2)
‘ Get custom document property
strVal = ActiveDocument.CustomDocumentProperties(“MyProp”).Value
‘ Test
If Not (strCell = strVal) Then
MsgBox “You ****** fool!”, vbExclamation
Cancel = True
End If
End Sub

Reply | Quote

Don’t tempt me – the number of times I’ve wanted to say “You ******* fool!” to a user who’s just done something particularly stupid … what I wouldn’t give to use that msgbox … just once …

Anyway, yup, I think I’ve got it now! Many thanks!

By the way, I’m feeling quite pleased with myself – the code I’d written to put in the BeforeClose was almost exactly what you gave, down to using the left and len commands to get rid of the table codes, etc! Maybe I’m actually beginning to get the hang of this code business!!

Reply | Quote

Don’t tempt me – the number of times I’ve wanted to say “You ******* fool!” to a user who’s just done something particularly stupid … what I wouldn’t give to use that msgbox … just once …

Anyway, yup, I think I’ve got it now! Many thanks!

By the way, I’m feeling quite pleased with myself – the code I’d written to put in the BeforeClose was almost exactly what you gave, down to using the left and len commands to get rid of the table codes, etc! Maybe I’m actually beginning to get the hang of this code business!!

Reply | Quote

Hans, I’m sorry, I’m sure this is me being stupid, but although I’ve got it all set up and as far as I can tell it works perfectly (when called ‘test’ and fired manually), it just doesn’t fire on closing the document? I have checked that the document I was trying it on is attached to it – is there something else I need to do? Put it in a document_close or something?

Just when I thought I was getting the hang of this …

Reply | Quote

Hans, I’m sorry, I’m sure this is me being stupid, but although I’ve got it all set up and as far as I can tell it works perfectly (when called ‘test’ and fired manually), it just doesn’t fire on closing the document? I have checked that the document I was trying it on is attached to it – is there something else I need to do? Put it in a document_close or something?

Just when I thought I was getting the hang of this …

Reply | Quote

Sorry, should have done this with the last post – herewith zipped copy of my template!

Reply | Quote

Sorry, should have done this with the last post – herewith zipped copy of my template!

Reply | Quote

To answer the question (you deleted?!) no, it didn’t – although I checked with breakpoints and the document_open one does …

Reply | Quote

Sorry, I deleted the question because your reply with attachment had appeared in the meantime.

The test in app_DocumentBeforeClose procedure is problematic. You don’t test for “-” or “On change”, and you should use Or instead of And, for if either of the conditions is met, the date is not valid. Finally, you omit to update the custom document property, since you only set the variable, not the property. And, by the way, since Next Review Date is a date variable, “-” and “On change” will be stored as 31/12/1899 (day 0 of the Windows calendar).

Here is a revised version:

Private Sub app_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
Dim tabString As Date, stringTab, propDate As Date
If ActiveDocument.AttachedTemplate = “ProcNew.dot” Then
stringTab = ActiveDocument.Tables(1).Cell(11, 2)
stringTab = Left(stringTab, Len(stringTab) – 2)
If stringTab = “-” Or stringTab = “On change” Then
propDate = ActiveDocument.CustomDocumentProperties(“Next Review Date”)
If tabString propDate Then
ActiveDocument.CustomDocumentProperties(“Next Review Date”) = tabString
End If
ElseIf Mid(stringTab, 3, 1) “/” Or Mid(stringTab, 6, 3) “/20” Or Len(stringTab) 10 Then
MsgBox “The ‘Next Review Date’ box either contains more than just a date or the” & vbLf & _
“date is not in the format ‘dd/mm/yyyy’. Please correct this. Thank you.”, vbInformation
Cancel = True
Else
tabString = Format(stringTab, “dd/mm/yyyy”)
propDate = ActiveDocument.CustomDocumentProperties(“Next Review Date”)
If tabString propDate Then
ActiveDocument.CustomDocumentProperties(“Next Review Date”) = tabString
End If
End If
End If
End Sub

This seems to work OK for me.

Reply | Quote

Hi Hans, thanks for the modifications, although I’m sure I would have discovered the lack the hard way at some point and had to sort the code then.

However, the problem I’m having at the moment is not that the content of the sub isn’t correct, but that it won’t fire in the first place!

When I create a document from the template and then close it, it just behaves normally; ie closes without comment if it’s saved, asks if I want to save if not, then closes. I put a breakpoint in it to check and it made no difference (although, as I said earlier, the template is definitely attached and the open and new subs do fire at their appropriate times). Your BeforeClose template behaved the same way (ie the sub didn’t fire – I didn’t get asked the question).

Thanks

Reply | Quote

Beryl,

I feel so ashamed! I have to apologize, for I had completely forgotten that Application in Word 97 doesn’t have a DocumentBeforeClose event; this was introduced in Word 2000. The only application events available in Word 97 are DocumentChange and Quit.

Sadly, I don’t know of a dependable alternative. You can create a macro named FileClose; this will intercept the File | Close menu item, but probably not its keyboard shortcut Ctrl+F4. It should intercept the close button x in the upper right corner of the document, but I can’t test that since Word 2002 behaves very different in that respect.

Reply | Quote

Ah – well, that would, of course, explain why it wouldn’t fire for me …! (At least it wasn’t just me being stupid!)

I don’t suppose there’s anything like an event that could be made to fire when a table cell is changed, instead? Preferably that works in Word97?!

Or maybe … could I put the date in a locked field and put a button beside it that they have to click if they want to change the date, instead?

I’ll have to get my thinking cap on again …

Reply | Quote

You could put the entire table in a protected section, and create a custom toolbar button and/or menu item that opens frmProperties; the code behind the OK button would have to unprotect the document, update the table, and protect the document again. But as you know, protecting a document disables lots of things in the unprotected sections too; this may be unacceptable.

Reply | Quote

Good idea, Hans, and feasible as long as the bits the user adds are not excessive, but this template is just to give a front page and assign properties for a new Procedure where all the rest of what could be a very lengthy document will be added by the user, so as you suggest, this instance, the ‘side effects’ of having a protected section in this case would be unacceptable.

anyway!

Reply | Quote

Good idea, Hans, and feasible as long as the bits the user adds are not excessive, but this template is just to give a front page and assign properties for a new Procedure where all the rest of what could be a very lengthy document will be added by the user, so as you suggest, this instance, the ‘side effects’ of having a protected section in this case would be unacceptable.

anyway!

Reply | Quote

I’ve decided to just make the msgbox say “if date’s not right, please reopen and correct” and trust them to do so. We’re supposed to be upgrading to Win/Office 2003 early next year ( ) so I can update it to use the BeforeClose then.

Just a quick question, if you don’t mind – how do you get the system to go back to the standard FileClose procedure after you’ve hijacked it? I mean, if you hijack FilePrint, you just put in Dialogs(wdDialogFilePrint).Show and the standard print window comes up, but there doesn’t seem to be an equivalent for FileClose. Or do you have to write your own little ‘do you want to save changes?’ etc routine?

Many thanks for all you help!

Reply | Quote

If you hijack FileClose, you can add an instruction ActiveDocument.Close after doing whatever you wanted to do. The default for Close is to prompt the user if the document has been changed; if you don’t like that, specify a SaveChanges argument. It can be wdDoNotSaveChanges, wdPromptToSaveChanges (default), or wdSaveChanges. For example, to save the document without asking:

ActiveDocument.Close SaveChanges:=wdSavechanges

Reply | Quote

Thanks, Hans, I knew there had to be something like that!

Reply | Quote

Thanks, Hans, I knew there had to be something like that!

Reply | Quote

Hans, in that template I posted, you probably noticed at one point I was taking information from a table cell (activedocument.tables(1).cells(11,2)) – well, I want to put information back into it now, but there doesn’t seem to be a ‘value’ variable for it and the debug keeps crashing on the line!

I’ve checked the help and although it doesn’t give any indication of how to specify ‘value’ it otherwise seems to indicate that I was right in the first place! ‘formula’ doesn’t work, I’ve tried it. What am I doing wrong?!

Reply | Quote

Hans, in that template I posted, you probably noticed at one point I was taking information from a table cell (activedocument.tables(1).cells(11,2)) – well, I want to put information back into it now, but there doesn’t seem to be a ‘value’ variable for it and the debug keeps crashing on the line!

I’ve checked the help and although it doesn’t give any indication of how to specify ‘value’ it otherwise seems to indicate that I was right in the first place! ‘formula’ doesn’t work, I’ve tried it. What am I doing wrong?!

Reply | Quote

Sorry, Hans, don’t worry – after posting that I went back and searched the help again, and this time I found it! ‘Working with tables’.

Apologies for wasting your time!

Reply | Quote

A late contribution to methods for intercepting closing of a document by a user. There are 3 Subs to “hijack”:
– DocClose intercepts the X-button of the document, Ctrl+W and Ctrl+F4
– FileClose interecpts menu File | Close
– FileExit intercepts menu File | Exit and Alt+F4

Jan

Reply | Quote

Now that is very useful info, JanB, thanks!

I don’t suppose you know the equivalents for intercepting all the variations of Open, Save and Print, do you? Or can you point me to somewhere that lists them?

Many thanks

Reply | Quote

I find the WordsCommands spreadsheet from the Word MVP site very useful. You can download it from Word for Windows commands, and their descriptions, default shortcuts and menu assignments.

Reply | Quote

It is indeed – many thanks, Hans!

Reply | Quote

It is indeed – many thanks, Hans!

Reply | Quote

I find the WordsCommands spreadsheet from the Word MVP site very useful. You can download it from Word for Windows commands, and their descriptions, default shortcuts and menu assignments.

Reply | Quote

Now that is very useful info, JanB, thanks!

I don’t suppose you know the equivalents for intercepting all the variations of Open, Save and Print, do you? Or can you point me to somewhere that lists them?

Many thanks

Reply | Quote

A late contribution to methods for intercepting closing of a document by a user. There are 3 Subs to “hijack”:
– DocClose intercepts the X-button of the document, Ctrl+W and Ctrl+F4
– FileClose interecpts menu File | Close
– FileExit intercepts menu File | Exit and Alt+F4

Jan

Reply | Quote

Sorry, Hans, don’t worry – after posting that I went back and searched the help again, and this time I found it! ‘Working with tables’.

Apologies for wasting your time!

Reply | Quote

If you hijack FileClose, you can add an instruction ActiveDocument.Close after doing whatever you wanted to do. The default for Close is to prompt the user if the document has been changed; if you don’t like that, specify a SaveChanges argument. It can be wdDoNotSaveChanges, wdPromptToSaveChanges (default), or wdSaveChanges. For example, to save the document without asking:

ActiveDocument.Close SaveChanges:=wdSavechanges

Reply | Quote

I’ve decided to just make the msgbox say “if date’s not right, please reopen and correct” and trust them to do so. We’re supposed to be upgrading to Win/Office 2003 early next year ( ) so I can update it to use the BeforeClose then.

Just a quick question, if you don’t mind – how do you get the system to go back to the standard FileClose procedure after you’ve hijacked it? I mean, if you hijack FilePrint, you just put in Dialogs(wdDialogFilePrint).Show and the standard print window comes up, but there doesn’t seem to be an equivalent for FileClose. Or do you have to write your own little ‘do you want to save changes?’ etc routine?

Many thanks for all you help!

Reply | Quote

You could put the entire table in a protected section, and create a custom toolbar button and/or menu item that opens frmProperties; the code behind the OK button would have to unprotect the document, update the table, and protect the document again. But as you know, protecting a document disables lots of things in the unprotected sections too; this may be unacceptable.

Reply | Quote

Ah – well, that would, of course, explain why it wouldn’t fire for me …! (At least it wasn’t just me being stupid!)

I don’t suppose there’s anything like an event that could be made to fire when a table cell is changed, instead? Preferably that works in Word97?!

Or maybe … could I put the date in a locked field and put a button beside it that they have to click if they want to change the date, instead?

I’ll have to get my thinking cap on again …

Reply | Quote

Beryl,

I feel so ashamed! I have to apologize, for I had completely forgotten that Application in Word 97 doesn’t have a DocumentBeforeClose event; this was introduced in Word 2000. The only application events available in Word 97 are DocumentChange and Quit.

Sadly, I don’t know of a dependable alternative. You can create a macro named FileClose; this will intercept the File | Close menu item, but probably not its keyboard shortcut Ctrl+F4. It should intercept the close button x in the upper right corner of the document, but I can’t test that since Word 2002 behaves very different in that respect.

Reply | Quote

Hi Hans, thanks for the modifications, although I’m sure I would have discovered the lack the hard way at some point and had to sort the code then.

However, the problem I’m having at the moment is not that the content of the sub isn’t correct, but that it won’t fire in the first place!

When I create a document from the template and then close it, it just behaves normally; ie closes without comment if it’s saved, asks if I want to save if not, then closes. I put a breakpoint in it to check and it made no difference (although, as I said earlier, the template is definitely attached and the open and new subs do fire at their appropriate times). Your BeforeClose template behaved the same way (ie the sub didn’t fire – I didn’t get asked the question).

Thanks

Reply | Quote

Sorry, I deleted the question because your reply with attachment had appeared in the meantime.

The test in app_DocumentBeforeClose procedure is problematic. You don’t test for “-” or “On change”, and you should use Or instead of And, for if either of the conditions is met, the date is not valid. Finally, you omit to update the custom document property, since you only set the variable, not the property. And, by the way, since Next Review Date is a date variable, “-” and “On change” will be stored as 31/12/1899 (day 0 of the Windows calendar).

Here is a revised version:

Private Sub app_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
Dim tabString As Date, stringTab, propDate As Date
If ActiveDocument.AttachedTemplate = “ProcNew.dot” Then
stringTab = ActiveDocument.Tables(1).Cell(11, 2)
stringTab = Left(stringTab, Len(stringTab) – 2)
If stringTab = “-” Or stringTab = “On change” Then
propDate = ActiveDocument.CustomDocumentProperties(“Next Review Date”)
If tabString propDate Then
ActiveDocument.CustomDocumentProperties(“Next Review Date”) = tabString
End If
ElseIf Mid(stringTab, 3, 1) “/” Or Mid(stringTab, 6, 3) “/20” Or Len(stringTab) 10 Then
MsgBox “The ‘Next Review Date’ box either contains more than just a date or the” & vbLf & _
“date is not in the format ‘dd/mm/yyyy’. Please correct this. Thank you.”, vbInformation
Cancel = True
Else
tabString = Format(stringTab, “dd/mm/yyyy”)
propDate = ActiveDocument.CustomDocumentProperties(“Next Review Date”)
If tabString propDate Then
ActiveDocument.CustomDocumentProperties(“Next Review Date”) = tabString
End If
End If
End If
End Sub

This seems to work OK for me.

Reply | Quote

To answer the question (you deleted?!) no, it didn’t – although I checked with breakpoints and the document_open one does …

Reply | Quote

Yes, you’d write the code in app_DocumentBeforeClose. Depending on the result of the test, set Cancel to True if you want to keep the document open. Something like:

Private Sub app_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
Dim strCell As String
Dim strVar As String
‘ Text of cell
strCell = ActiveDocument.Tables(1).Cell(2, 3).Text
‘ Get rid of cell marker
strCell = Left(strCell, Len(strCell) – 2)
‘ Get custom document property
strVal = ActiveDocument.CustomDocumentProperties(“MyProp”).Value
‘ Test
If Not (strCell = strVal) Then
MsgBox “You ****** fool!”, vbExclamation
Cancel = True
End If
End Sub

Reply | Quote

Right, I think I’ve got all of it except one little bit – the actual code I want to run before the document closes – where do I put this? Is it under the sub app_documentbeforeclose bit?

Muchos gracias!

Reply | Quote