Sequential numbering

utterly confused

New Member
Joined
Nov 23, 2005
Messages
28
Great forum, have received excellent help but now need some more!!

Have created an invoice with an auto numbering, save, print and exit which is working great. However the problem is that if we open the saved files it continues to count or if we open the invoice and then not use it it is still counting on. What I need is some sort of prompt to ask if we wish to count on by one and then when we say yes it counts on.

At present I am using the following code:-

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Sheets("Sheet1").Unprotect
Sheets("Sheet1").Range("H13").Value = Sheets("Sheet1").Range("H13").Value + 1
Sheets("Sheet1").Protect
ActiveWorkbook.SaveAs "P:\K.B\K.B\OFFICE FORMS\kb templates\galv order request.xlt"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim fpath As String, fName As String
fpath = "P:\K.B\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
ActiveWorkbook.PrintOut
End Sub


Can anyone give me any ideas? Cheers
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fat Cat

Active Member
Joined
Nov 5, 2004
Messages
336
You could try this;

Code:
Private Sub Workbook_Open()

Dim ans As Integer

    Sheets("Sheet1").Select
    Sheets("Sheet1").Unprotect
    ans = MsgBox("Do you want to generate a new Invoice Number ?", vbYesNo, "New Invoice ?")
    Select Case ans
    Case 6          'this is a YES ans
        Sheets("Sheet1").Range("H13").Value = Sheets("Sheet1").Range("H13").Value + 1
        Sheets("Sheet1").Protect
        ActiveWorkbook.SaveAs "P:\K.B\K.B\OFFICE FORMS\kb templates\galv order request.xlt"
    Case Else       'this is a NO ans or cancelled
    End Select
    
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim fpath As String, fName As String

    fpath = "P:\K.B\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
    ActiveWorkbook.PrintOut
    
End Sub
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
If I understand you correctly, how about adding the following in your Workbook_Open event code ....
Code:
....   
    Message = "Enter 1 and OK to crete a new Invoive, 0 and OK or Cancel to skip"
    Title = "Increment Invoice Number"

    MyValue = InputBox(Message, Title, 1)
    If MyValue = "1" Then [H13] = [H13] + 1
....
 

Watch MrExcel Video

Forum statistics

Threads
1,119,127
Messages
5,576,235
Members
412,709
Latest member
AD04
Top