Solved: Document_Save Event Handler

[icthus123]

Is there anyway to have a sub procedure that is triggered by saving an MS Word document? Thanks!

[lucas]

In a standard module:
Sub FileSave()
MsgBox "Your message Here."
End Sub

[icthus123]

In a standard module:
Sub FileSave()
MsgBox "Your message Here."
End Sub

Thanks a lot lucas! That's great, is there any way I can stop it appearing in the macro box? Because:

Option Private Module

doesn't work?

[lucas]

Not that I am aware of....I tried several things including adding it to thisdocument to no avail. Maybe Gerry or one of the regular Word guys will have an idea.

[icthus123]

Not that I am aware of....I tried several things including adding it to thisdocument to no avail. Maybe Gerry or one of the regular Word guys will have an idea.

Okay, thanks a lot for your help lucas! I'll leave this thread open a while and see if anyone replies!

[matthewspatrick]

The Word.Application object exposes a DocumentBeforeSave event
that you could use. You will need to implement a class to get at it,
though.

Go to the VB Editor, and in the help search on Application Events for
an article on how to expose those events in VBA.

[fumei]

Or, you could also disable getting the macros dialog box.

Sub ToolsMacro()
' do nothing
' disables Tools > Macro > macros
' AND Alt-F8
End Sub

[icthus123]

Thanks guys! I've ended up with a template! However, when I try to use it as a template I find that the macros don't seem to work! That is when I open it I still get the enable macro's warning, but the combo boxes aren't being populated! What is the problem?

[fumei]

As this has nothing to do with what you started the thread with, how are we to really know? Youy never mentioned it. A good place to start is to post what your code is to populate. Also, be specific with terms like "open" and "use".

Does that mean, specifically, you cloned a new document with File > New (the way to "use" templates) and when the new document is "opened" the combobox does not populate?

None of which has anything to do with saving the file - the topic you posted for the thread.

[icthus123]

Does that mean, specifically, you cloned a new document with File > New (the way to "use" templates) and when the new document is "opened" the combobox does not populate?


That is what I mean. Any ideas why that is?



None of which has anything to do with saving the file - the topic you posted for the thread.


I know! I just thought I'd tag it on the end in case anyone had any ideas instead of starting I new thread. But I'm happy to start a new thread if that would be better!

[fumei]

No, as you did not bother to post any code. Nope sorry....haven't a clue. It is hard to have idea when I have NO idea what you are currently doing.

[icthus123]

No, as you did not bother to post any code. Nope sorry....haven't a clue. It is hard to have idea when I have NO idea what you are currently doing.

Okay I've attached a document which was based on the template. Can you see what the problem may be now?

[icthus123]

Okay I've attached a document which was based on the template. Can you see what the problem may be now?

Okay that didn't work! Here it is again!!

[lucas]

you need to post the pprTemplate.dot

There is no code in the doc file you posted.

[icthus123]

you need to post the pprTemplate.dot

There is no code in the doc file you posted.

I tried to post that but this website won't upload .dot files for some reason! but I think the code is there under Project (PPRTemplate) > Microsoft Word Objects > ThisDocument, or it is when I open the link anyway!

[lucas]

No code in the document you posted. Try zipping the .dot file and post it.

[icthus123]

No code in the document you posted. Try zipping the .dot file and post it.

Okay, here it is!! I don't know why I didn't think of that!

[lucas]

I find comboboxes referenced but I don't see them...
cboNIC.AddItem arrAll
no comboboxes in the files you have posted.

Question: Are you trying to populate specific places in the sheet using document properties? Why not use bookmarks?

[lucas]

I have to leave for a while so be patient. I also see no userform in either of the documents you posted.

[icthus123]

I have to leave for a while so be patient. I also see no userform in either of the documents you posted.

okay thanks, there isn't any userform. The combo boxes are on the page and the borders are hid. The combo boxes are on the first page where it says no.

[lucas]

You had your code in the document open procedure. For templates to run the code on the newly cloned document you need to put it in the document new procedure.....see attached.

[fumei]

icthus123, you should really be using Select Case instead of all those If...Then statements.

You have eight DocProperties you are checking:

NIC
Boiler
Gas
Fire
Asbestos
Light
FireRisk
Ramp

You have the following code, a bunch of If..Then statements:For Each objProp In _
ActiveDocument.CustomDocumentProperties
If objProp.Name = "NIC" Then
' yadda
End If
If objProp.Name = "Boiler" Then
' yadda
End If
If objProp.Name = "Gas" Then
' yadda
End If
If objProp.Name = "Fire" Then
' yadda
End If
If objProp.Name = "Asbestos" Then
' yadda
End If
If objProp.Name = "Light" Then
' yadda
End If
If objProp.Name = "FireRisk" Then
' yadda
End If
If objProp.Name = "Ramp" Then
' yadda
End If
NextNow suppose the objProp IS "NIC". The first If executes and you do your stuff ("yadda"). HOWEVER, that objProp also executes ALL the other If..Then statements. EACH objProp will execute EIGHT different If...Then statements, regardless.

Which makes a total of 64 individual If..Then instructions carried out. In each case, the VBA parser checks for the value of objProp.Name. It does not remember the Name. Once each If...Then statement ends (with End If), the VBA parser starts all over again. It has to go and check the value of objProp.Name each time. 64 times.

So again, if the objProp IS "NIC" (and it does the other instructions), it still executes:

If objPro.Name = "Fire"
If objPro.Name = "Gas"
If objPro.Name = "Light"

etc. etc. 64 instructions are executed.

What are you actually checking? objProp.Name Whenever you have possible multiple values for the same variable use Select Case. Here it is.For Each objProp In ActiveDocument.CustomDocumentProperties
Select Case objProp.Name
Case "NIC"
bolExistsNIC = True
Set objPropNIC = _
Me.CustomDocumentProperties.Item("NIC")
Case "Boiler"
bolExistsBoiler = True
Set objPropBoiler = _
Me.CustomDocumentProperties.Item("Boiler")
Case "Gas"
bolExistsGas = True
Set objPropGas = _
Me.CustomDocumentProperties.Item("Gas")
Case "Fire"
bolExistsFire = True
Set objPropFire = _
Me.CustomDocumentProperties.Item("Fire")
Case "Asbestos"
bolExistsAsbestos = True
Set objPropAsbestos = _
Me.CustomDocumentProperties.Item("Asbestos")
Case "Light"
bolExistsLight = True
Set objPropLight = _
Me.CustomDocumentProperties.Item("Light")
Case "FireRisk"
bolExistsFireRisk = True
Set objPropFireRisk = _
Me.CustomDocumentProperties.Item("FireRisk")
Case "Ramp"
bolExistsRamp = True
Set objPropRamp = _
Me.CustomDocumentProperties.Item("Ramp")
End Select
NextNow what happens? If objProp.Name = "NIC", then it finds the case for THAT value, and does the instructions....and goes on to the next objProp.

Total instructions (ie. checking objProp.Name)? 8...not 64.

Further, I fail to see the need to declare all those document property objects.Dim oProp As DocumentProperty

Dim msg As String
For Each oProp In ActiveDocument.CustomDocumentProperties()
msg = msg & oProp.Name & " = " & oProp.Value & vbCrLf
Next
MsgBox msgThis will display (from what you have as defaults in your file):

NIC = Yes
Boiler = No
Gas = Yes
Fire = No
Asbestos = Yes
Light = Yes
FireRisk = No
Ramp = Yes

So you can get at your custom values without going through all that code.

If you need to make an array of them, then:Public myCustomProps()

Sub Whatever()
Dim oProp As DocumentProperty
Dim var

For Each oProp In ActiveDocument.CustomDocumentProperties()
ReDim Preserve myCustomProps(var)
myCustomProps(var) = oProp.Name
var = var + 1
Next

' now you have an array and can call to it, OR
' you can make ONE object whatever Property you want
Set oProp = ActiveDocument.CustomDocumentProperties _
(myCustomProps(3))
MsgBox oProp.Name & " = " & oProp.Value
End SubThis would display the fourth (the array is 0-based) item...

Fire = No

[icthus123]

You had your code in the document open procedure. For templates to run the code on the newly cloned document you need to put it in the document new procedure.....see attached.

Thanks a lot! That's solved that! The only problem now is that it doesn't work when I open a document I'd previously saved using the template! :dunno Now I guess I could get round this by putting the sub procedure into a separate sub and calling it in Document_New() and Document_Open(). This seems to work well. Is this the best way round this?

[icthus123]

Thanks a lot fumei, that's a lot better! However, I'm not totally sure I get what you're suggesting I do in the bit below? :think:


Further, I fail to see the need to declare all those document property objects.Dim oProp As DocumentProperty

Dim msg As String
For Each oProp In ActiveDocument.CustomDocumentProperties()
msg = msg & oProp.Name & " = " & oProp.Value & vbCrLf
Next
MsgBox msgThis will display (from what you have as defaults in your file):

NIC = Yes
Boiler = No
Gas = Yes
Fire = No
Asbestos = Yes
Light = Yes
FireRisk = No
Ramp = Yes

So you can get at your custom values without going through all that code.

If you need to make an array of them, then:Public myCustomProps()

Sub Whatever()
Dim oProp As DocumentProperty
Dim var

For Each oProp In ActiveDocument.CustomDocumentProperties()
ReDim Preserve myCustomProps(var)
myCustomProps(var) = oProp.Name
var = var + 1
Next

' now you have an array and can call to it, OR
' you can make ONE object whatever Property you want
Set oProp = ActiveDocument.CustomDocumentProperties _
(myCustomProps(3))
MsgBox oProp.Name & " = " & oProp.Value
End SubThis would display the fourth (the array is 0-based) item...

Fire = No

[icthus123]

Okay, now the procedure under the DocumentBeforeSave Event isn't working. And I don't think it's particularly because I've done that wrong, because it doesn't work when I change it to FileSave() or Document_Close() either. Any ideas? I've attached it again.

[lucas]

document close works.....not sure about the code you're running

[icthus123]

document close works.....not sure about the code you're running

oh right! :think: what do you think could be wrong with my code?

[icthus123]

oh right! :think: what do you think could be wrong with my code?

actually the code's fine! The reason why it didn't seem to be working with Document_Close() was because I was saving and then closing, so my Custom Document Properties weren't being saved when it closed.

So all the code's fine now! The DocumentBeforeSave Event just doesn't work!! Which brings us right back to why I started this post? Why doesn't it work? I think I've probably just missed something off! :doh:

[lucas]

Nothing....it runs fine if you put it in the document close statement

[lucas]

You have to qualify the controls, etc. specifically. This works on save.

me does not work

[icthus123]

You have to qualify the controls, etc. specifically. This works on save.

me does not work

Right that's great! Is there anyway I can use the:


Public WithEvents appWord As Word.Application
Private Sub appWord_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)


though? Cause at the moment FileSave is in the list of macros!

[lucas]

Not if you use the file as a template.....

double click the .dot file and look at the top of the page...it should say document 1 or something like that.....it should not read as the name of the template.....that is what your users will see.

[icthus123]

Not if you use the file as a template.....

double click the .dot file and look at the top of the page...it should say document 1 or something like that.....it should not read as the name of the template.....that is what your users will see.

I get doc 1, but it still has FileSave in the list of macros! :think:

[lucas]

I don't know how to help with this.....I refer you to post#6 & #7 of this thread.......

The following does not run the code(I think it is just running a boolian routine....eg true or false)
Private Sub appWord_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)

why is it so important that they can't get to the code and why don't you just password protect the project.....that way they can see it and run it but cannot access the code to look at it or change it?

[icthus123]

I don't know how to help with this.....I refer you to post#6 & #7 of this thread.......

The following does not run the code(I think it is just running a boolian routine....eg true or false)
Private Sub appWord_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)

why is it so important that they can't get to the code and why don't you just password protect the project.....that way they can see it and run it but cannot access the code to look at it or change it?

Well, I guess it's not that important really! I just think it looks more tidy if you can't see it in macro box! But thanks a lot for your help Steve! I'll probably stick with what I've got now! I'll just leave the thread open a while in case anyone has any ideas! :beerchug:

[icthus123]

Okay, I think I've made quite a bit of progress with this DocumentBeforeSave Event. But I still can't get it to work!! Can someone have a look at what I've done and tell me where i'm going wrong? It's in the class module ThisApplication! Thanks!

[lucas]

this is wrong...me won't work:
Me.CustomDocumentProperties("NIC") = strNIC
this won't work either as it doesn't say where cboNIC is located
strNIC = cboNIC.Value

I have shown you how to qualify these in an earlier post.....
I believe it was activedocument
for instance:
strNIC = activedocument.cboNIC.Value

[lucas]

I'm just going to throw this out there this one time.....why don't you do this in excel.....it would be so simple

[icthus123]

Okay thanks a lot for all your help guys! I've got it sorted now!

I guess, I could have done it in excel, except that it was in excel before and the reason we moved it into word was because the actual writing the report will be much easier in word!