Solved: How can I alter this code?

[eliW]

Please look at my post in Office Experts Forum and Dreamboat's answer using very nice code:

http://www.theofficeexperts.com/forum/showthread.php?t=5093



Now I need your help to alter this code in a way that:
(1) documents won't be saved automatically.
(2) only after saving, oNum will be advanced by 1
(3) if the document is not saved, oNum remains the same.

Here is the code:

Thank you in advance,

Eli
Private Sub Document_New()
'
'Note the location where you create your text file
'must be changed in two places in this code - the following line and...
oNum = System.PrivateProfileString("C:\increment.txt", "InvNmbr", "oNum")
If oNum = "" Then
oNum = 1
Else
oNum = oNum + 1
End If
'you must also change the text file path here (next line) as well
System.PrivateProfileString("C:\increment.txt", "InvNmbr", "oNum") = oNum
ActiveDocument.Bookmarks("oNum").Range.InsertBefore Format(oNum, "00#")
ActiveDocument.SaveAs FileName:="path" & Format(oNum, "00#")
End Sub

[Ken Puls]

Hi Eli,

I haven't tested the code, but I think that this should work for your purposes. If not, please post back with what is going wrong, and either I or someone else can look into it for you. :)

Private Sub Document_New()
'
'Note the location where you create your text file
'must be changed in two places in this code - the following line and...
oNum = System.PrivateProfileString("C:\increment.txt", "InvNmbr", "oNum")
If oNum = "" Then
oNum = 1
Else
If MsgBox("Would you like to save this document?", vbYesNo, "Save?") = vbYes Then
'you must also change the text file path here (next line) as well
System.PrivateProfileString("C:\increment.txt", "InvNmbr", "oNum") = oNum
ActiveDocument.Bookmarks("oNum").Range.InsertBefore Format(oNum, "00#")
ActiveDocument.SaveAs FileName:="path" & Format(oNum, "00#")
oNum = oNum + 1
End If
End If
End Sub

PS, I edited you post above to use our VBA tags. It saves you having to apply a bunch of formatting to the post.

[eliW]

Hi kpuls,
Appreciate your concern, but no, that is not what I really ment.
Your code brings up a msgbox once, before even starting to create the document. now supose I am intending to save it but then I decide to drop it....the numerator will advance. I am looking for a code that will advance the numerator only when attempting to save at the first time.
Can it be done?
Again, thank you.
Eli

[TonyJollans]

Really you want to run this code when saving and not when opening the document. The only way to do this (and catch all possible way of saving) is to use the Application DocumentBeforeSave Event.

Application Events need a bit of setting up - and they affect every open document so need some extra checks in the code.

In your Template, create a new Class Module and enter this code:

Public WithEvents App As Word.Application

Private Sub App_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)

' Don't do for docs not based on template
If Doc.AttachedTemplate <> MacroContainer Then Exit Sub

' Don't do for template itself
If Doc = MacroContainer Then Exit Sub

' Only do for first save
If Doc.Path <> "" Then Exit Sub


' This just your code cut and pasted into here
'
'Note the location where you create your text file
'must be changed in two places in this code - the following line and...
oNum = System.PrivateProfileString("C:\increment.txt", "InvNmbr", "oNum")
If oNum = "" Then
oNum = 1
Else
oNum = oNum + 1
End If
'you must also change the text file path here (next line) as well
System.PrivateProfileString("C:\increment.txt", "InvNmbr", "oNum") = oNum
ActiveDocument.Bookmarks("oNum").Range.InsertBefore Format(oNum, "00#")
ActiveDocument.SaveAs FileName:="path" & Format(oNum, "00#")
' End of pasted code

' Stop Word doing the save - you've already done it
Cancel = True

End Sub

In the ThisDocument Module of the Template add this code:

Dim ThisAppTrap As New AppEventTrap

Private Sub Document_New()
Set ThisAppTrap.App = Word.Application
End Sub

[TonyJollans]

Hi eliW,

Welcome to VBAX!

Didn't say above but this is to replace your existing code (not in addition to it).

[eliW]

In your Template, create a new Class Module and enter this code:


Hi Tony and thank you,

Where exactly should I put this code? how do I create new Class Module?

Eli

[Ken Puls]

Hi Eli,

To create the Class module, go into the VBE (Alt + F11). If you don't see the project explorer open (looks like the windows tree), press Ctrl + R to show it.

Find your template, (it will probably say Project(Filename),) and right click it. Choose Insert-->Class Module.

Make sure that the blocks of code Tony gave you go into the right modules (as per his instructions.) That's really important or it won't work correctly.

HTH,

[TonyJollans]

A correction, I'm sorry.

Just catching up and I noticed I forgot to say something.

After you have created your Class Module you need to rename it for the code as posted to work. By default it will (probably) be called "Class1" but the code assumes it's called "AppEventTrap".

Easiest thing is to change the code you put in the ThisDocument to Dim ThisAppTrap As New Class1

Private Sub Document_New()
Set ThisAppTrap.App = Word.Application
End Sub

[eliW]

Thank you very much, kpuls and Tony, It works now thanks to you.