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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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
....
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,621
Messages
5,832,735
Members
430,160
Latest member
a_majda

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
Top