more help sequential numbering

[utterly confused]

Have put a few messages on re this now but still need some help to get it running right.....I have the following in an invoice template...

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Sheets("Sheet1").Unprotect
Sheets("Sheet1").Range("H13").Value = Sheets("Sheet1").Range("H13").Value + 1
Sheets("Sheet1").Protect
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim fpath As String, fName As String
fpath = "P:\K.B\GB Galvanising orders\"
fName = Sheets("Sheet1").[B13].Value & " - " & _
Sheets("Sheet1").[H14].Text & " - " & _
Sheets("Sheet1").[H13].Value & ".xls"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fpath & fName
Application.DisplayAlerts = True
End Sub


The only problem is that the numbering will not count on one from the template. Only counts on if I go in and open the last invoice done and saved which I don't want to do. Want all users to be able to open the invoice from a shortcut on their screen and fill it out. Can anyone help I am sure it is only something simple.

Also is there anything I can add to have this print automatically upon closing?

Please help have been working on this for days and days. Thanks in advance!!!!

 

[farnuckl]

The counter on your template isn't working because you don't save the template itself, you are "save as"-ing a new workbook.

Here is a line that I use to print. You should be able to add this after your save line in the BeforeClose sub.

ActiveWorkbook.PrintOut ActivePrinter:="HP LaserJet 4200 PCL 6 on Ne01:"

I am on a network, so I need the Activeprinter designation at the end, you may only need Activeworkbook.PrintOut

-farnuckl

 

[utterly confused]

Thanks muchly for your reply! Haven't saved the template itself but rather the invoice to a file as I need a permanent record of it....can you help me on how to do it differently. Thanks again.

 

[farnuckl]

Can you explain in more detail what you would like to do? I'm not sure I understand your last post.
If you want to save the template, you would just use

activeworkbook.save

along with the save as to create the copy.

-farnuckl

 

[utterly confused]

Sorry if I am not making myself clear, am a complete novice at all of this.

What I am trying to do is an invoice/order template that updates a number by one each time the template gets opened, print it out and then save the invoice to a file. I need the template to be opened each time a new one is being done not the last invoice saved to the file. The numbering system is only working if I open the last saved invoice not the template.

Does this make more sense.?

 

[farnuckl]

Yes, that is what I thought you meant. Did you try my suggestion? You should just need to save your template before you save as to get the invoice.

-farnuckl

 

[utterly confused]

Have tried the activeworkbook.save just before the line that states dimfpath, the counter still not working when I open up the template again. Any ideas would be greatly appreciated.

 

[farnuckl]

I believe I've figured it out. The BeforeClose sub needs to be in the ThisWorkbook code. In VBAProject screen right click on ThisWorkbook under Microsoft Excel Objects and view code. Just copy and paste your current code into the ThisWorkbook code and it should work just right.

-farnuckl

 

[utterly confused]

I know this is being a real pain but it is already in the THisWorkbook code, that is where I have all the code.

 

[farnuckl]

Well, then I don't know what else to tell you. That is where I put my testing code and it works fine for me...

Option Explicit

Private Sub Workbook_BeforeClose(cancel As Boolean)

Dim path As String

path = "c:\blah\blah\"

Range("a2").Value = Range("a2").Value + 1

ActiveWorkbook.Save
ActiveWorkbook.SaveAs (path & "book" & Range("a2").Value & ".xls")

End Sub

Are you sure you have it in the ThisWorkbook code, not a module?

-farnuckl