more help sequential numbering

utterly confused

New Member
Joined
Nov 23, 2005
Messages
28
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!!!!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

farnuckl

Board Regular
Joined
Dec 16, 2003
Messages
127
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.

Code:
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

New Member
Joined
Nov 23, 2005
Messages
28
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

Board Regular
Joined
Dec 16, 2003
Messages
127
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
Code:
activeworkbook.save
along with the save as to create the copy.

-farnuckl
 

utterly confused

New Member
Joined
Nov 23, 2005
Messages
28

ADVERTISEMENT

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

Board Regular
Joined
Dec 16, 2003
Messages
127
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

New Member
Joined
Nov 23, 2005
Messages
28

ADVERTISEMENT

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

Board Regular
Joined
Dec 16, 2003
Messages
127
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

New Member
Joined
Nov 23, 2005
Messages
28
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

Board Regular
Joined
Dec 16, 2003
Messages
127
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...

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,128
Messages
5,576,251
Members
412,709
Latest member
Rishu
Top