Help - invoice sequential numbering

[utterly confused]

Trying to create, with much stress and anxiety, an order form with automated numbering. Being a complete and utter novice at VBA I copied the following from somewhere on this web site.



Private Sub Workbook_Open()
Dim nmbr As Long
Dim fNum As Integer
fNum = FreeFile
Open ThisWorkbook.Path & "\" & "lastvalue.txt" For Random As #fNum Len = Len(nmbr)

Get #fNum, 1, nmbr
nmbr = nmbr + 1
Put #fNum, 1, nmbr
Close #fNum
If Sheets("Sheet1").Range("A1").Value = "" Then Sheets("Sheet1").Range("A1").Value = nmbr
End Sub



I changed A1 to H13 as H13 is the cell I need the order number to go into.



My problem is a couple of things not working as they should.



Firstly I need to have the notepad save to a “public” drive and not my local drive. When I try to do this it does not seem to like it.



Secondly I need to change the number in the notepad to reflect what invoice number we are up to such as 11780, however the number on the invoice form comes up as something unrelated and much much higher. When I go back to Notepad the numbers appear spaced, ie: y 1 1 7 8 0, and a letter y has been added.



Lastly is it possible to run a macro to prompt the operator to save not as a template but to a file?


As I said am a complete novice so hope this all makes sense, thanks in anticipation of some stress relief!!

 

[Derek]

Hi there

Hi there

He's a slightly different approach. Go to the VB editor, in the projects window doubleclick on ThisWorkbook and paste this code in the large window.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Answer = MsgBox("Confirm Invoice Number?", vbOKCancel, "CONFIRMATION")
If Answer = vbOK Then
Range("IV1").Value = Range("A1").Value
ActiveWorkbook.Save
Exit Sub
End If
If Answer = vbCancel Then
Application.DisplayAlerts = False
Exit Sub
End If
End Sub

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Range("A1").Value = Range("IV1").Value + 1
End Sub

This assumes Sheet1 is your invoice sheet and the Invoice Number is in A1.
The number in A1 will increase by 1 each time you open the sheet. When you close the sheet it will ask you to confirm that number, If you do not confirm then the same number will appear next time you open the workbook.

regards
Derek

 

[utterly confused]

Thanks Derek,

Have done this but still have a few problems. I have the sheet protected due to other users it is now giving me a run-time error 1004 because of this and not allowing it to save.

Secondly where would i put the number that I wish the invoices to start from, assuming my invoice no. is appearing in cell H13...probably very basic questions but too many hours spent on this has baked my brain!!

 

[Derek]

Hi again

Okay, if you want to protect your worksheet, paste this code instead:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Answer = MsgBox("Confirm Invoice Number?", vbOKCancel, "CONFIRMATION")
If Answer = vbOK Then
Sheets("Sheet1").Unprotect "Your Password here"
Range("IV1").Value = Range("A1").Value
Sheets("Sheet1").Protect "Your Password here"
ActiveWorkbook.Save
Exit Sub
End If
If Answer = vbCancel Then
Application.DisplayAlerts = False
Exit Sub
End If
End Sub

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Sheets("Sheet1").Unprotect "Your Password here"
Range("A1").Value = Range("IV1").Value + 1
Sheets("Sheet1").Protect "Your Password here"
End Sub


In the places the code says "Your Password here" you need to replace those words with your own password.
The invoice number appears in A1 of Sheet1. If your sheet has a different name you will need to change that in the code wherever it appears.
If you want your Invoice number in a different cell, you need to change "A1" in the code to the cell you want.

regards
Derek

 

[utterly confused]

Thanks so much, working beautifully....one more question, is there a macro to prompt the users to save it to a file, ie : to save them from going to 'save as', a couple of the users are more novice than I am so wanting to keep it as simple as possible!! Thanks

 

[Derek]

Try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
answer = MsgBox("This Workbook MUST be saved in its original name" & vbCr & "Confirm Invoice Number?", vbOKCancel, "CONFIRMATION")
If answer = vbOK Then
Sheets("Sheet1").Unprotect "Your Password here"
Range("IV1").Value = Range("A1").Value
Sheets("Sheet1").Protect "Your Password here"
ActiveWorkbook.Save
Exit Sub
End If
If answer = vbCancel Then
Application.DisplayAlerts = False
ActiveWorkbook.Save
Exit Sub
End If
End Sub

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Sheets("Sheet1").Unprotect "Your Password here"
Range("A1").Value = Range("IV1").Value + 1
Sheets("Sheet1").Protect "Your Password here"
MsgBox "When you close this workbook it will automatically save" & vbCr & vbCr & "DO NOT SAVE AS ANYTHING ELSE"
End Sub

regards
Derek

 

[utterly confused]

Hi Derek,

Thanks for your reply from last week and your help, have been away for couple of days and just got back into this dreaded invoice again....still having a few problems.

Firstly need to save to a particular file so I am assuming somewhere in the last code you gave me there will need to a path name? I also don't need to have it protected by a password

When I run this invoice the number is not increasing by 1 each time, it did do this a couple of times last week but now does not appear.

Any ideas? Thanks