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!!!!
 
This is what I've got now. A problem arose with the invoices retaining the code that increments the counter. The extra code at the bottom deletes all the code from the invoice before saving it (while retaining the code in the template.) You'll need to follow some of the directions in this link:
http://www.cpearson.com/excel/vbe.htm
regarding loading the appropriate reference library and adjusting your security settings.

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")

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
   Select Case VBComp.Type
      Case vbext_ct_StdModule, vbext_ct_MSForm, _
            vbext_ct_ClassModule
         VBComps.Remove VBComp
      Case Else
         With VBComp.CodeModule
            .DeleteLines 1, .CountOfLines
         End With
   End Select
Next VBComp

End Sub

-farnuckl
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
It appears to be in ThisWorkbook code, when I right click on thisworkbook and view code that is where is appears. Thanks for your help anyway I will just have to keep working on it to see if I can get it working.
 
Upvote 0
Thanks heaps for all your help, this last bit has left me all the more confused, think should just go back to filling in the manual sheet to do this work!! I think I too much of a novice to even attempt the VB Component stuff.
Maybe will just try to run it from the last invoice and not worry about a template, the numbering system works fine then, just need to clear out/change the contents on the last invoice.

Again my thanks
 
Upvote 0
Sorry I couldn't be of more help. But keep trying, everything I know is from this board and experimenting. Sometimes not knowing how something works doesn't mean you can't use it if it works. :wink:

Good luck!

-farnuckl
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top