Sequential numbering

[utterly confused]

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

 

[Fat Cat]

You could try this;

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]

If I understand you correctly, how about adding the following in your Workbook_Open event 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
....

 

[utterly confused]

Works like a dream thank you so very much for your help, my boss is very impressed!!!