Sequential Number for every time the excel workbook is opened. HELP!!!!!!

[Omkar Balgobin]

Hi guys,
I have an excel workbook that I use as a quotation template with three sheets, I have a Macro that forces "Save As" and disables "Save" to ensure that the user saves a new version of their "Quotation" for easy retrieval (I will paste the SaveAs macro below).

Now I would like to include a quotation number that will automatically change every time the documet is opened. Eg: if the user opens the quotation template and the last quote number was MTBP 8/17 the new quote number on the opened template will be MTBP 9/17;
MTBP: Company Initials
8:Quote Number
/17: Quote Year

To this point I am successful, however this only work if I continue "Saving" over the same template and not "SaveAs". Basically if I use "Save As" the newly saved quotation will have the updated quote number and the template will still carry the number it originally had;
Quote Template Quote #: MTBP 8/17
New Saved As Quote #: MTBP 9/17

Can any of you guys help me with this, if it is possible?

SAVE AS MACRO:

Dim xFileName As String

SaveAs:
If SaveAsUI <> True Then
xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As")
If xFileName <> "False" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
Else
ans = MsgBox("Are you sure you want to Cancel?", vbYesNo, "Cancel Alert")
Select Case ans
Case vbYes: Cancel = True: Exit Sub
Case vbNo: GoTo SaveAs
End Select
End If
End If

End Sub

SEQUENTIAL QUOTE # MACRO

Private Sub Workbook_Open()
Range("D2").Value = Range("D2").Value + 1
End Sub

NB I use the following formula to get my desires quote number layout;

="MTBP"&" "&D2&"/"&TEXT(TODAY(),"YY")

I will appreciate your generous assistance.


Kind regards,
OB

[Bernie Deitrick]

After your code updates the quote number on the template, you need to do a Save and then a SaveAs or SaveCopyAs

[Omkar Balgobin]

Bernie,
Thank you for responding, I being a NEWBIE is the understatement of the year, my experience comes from Google searches and you guys, which I can get by with.

Can you elaborate the codes required a bit;
RE: Save and then a SaveAs or SaveCopyAs

Regards,
OB

[Bernie Deitrick]

Just change add the save right before this line

ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Like so:

ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

[Omkar Balgobin]

Bernie,
Thank you I made the changes advised, it worked , however it lead to another issue;

If during the save process the user decides to cancel the save function the template quote number still adds 1.
I tried the following scenarios;
1. Click close → Save → Cancel → Yes → Close document. (On reopening the quotation template the quote # + 1)
2. Click save → Cancel → Yes → Close document. (On reopening the quotation template the quote # + 1)
3. Click save as → Cancel → Close document. (On reopening the quotation template the quote # stays the same)

If have two questions;
1 - Any suggestion to resolve the above issue?
2 - Is the a macro to grey out the save button or reprogram it to command save as?


I appreciate your assistance, I have attached a sample of my workbook.

Regards,
OB

[Bernie Deitrick]

Case vbYes: Cancel = True: Range("D2").Value = Range("D2").Value - 1: Exit Sub

[Omkar Balgobin]

Bernie,
Where in the macro do I apply this code, also should there be a select somewhere in there?

On a related note, the ActiveWorkbook.Save applies the save to all the changes made to the template, I only require the save to be applied quote #.

Is this possible?

Regards,
OB

[Bernie Deitrick]

You need to use a different approach is you are going to allow the users to cancel.
Try this: change your save code to

Please Login or Register  to view this content.

[Omkar Balgobin]

Bernie,
Thank you very much, this worked great. Thank you very much.

I have two new issues arising out of this;

ISSUES #1.
1.1 I have created a template that is my master document QUOTE # 100, when I open the master template it is opened at 101 .
1.2 Then when I make some changed and try to save it saves the new quote as 101 (or whatever name I decides) this saves fine .
1.3 After there is a second Save As prompt where the changes I made to the template are cleared, if I select save I "run time error 91", Then I select end and the master template remains open with quote # 101.
1.4 Then If I try to close the template with the number 101 I get the prompt to save yes/no basically I can select Yes: Save the template with the number 101 or NO: Lose the change and the document will revert to quote #100.

Question;
Q1: How do I resolve the "run time error 91"?
Q2: 1.4 seems to be either a redundant save as step or a duplicated step can this be removed to achieve the same end result?

ISSUES #2.
On opening the saved quote # 101, the quote opens as 102 and NOT as 101 as it should have opened.
Q1. Is there a was to disable the sequential numbering macro for the save quotation 101?

I will appreciate any assistance that can be lent on this, I have attached the two samples that I worked on
1. Template
2. Quote 101


Kind regards,
OB

[Omkar Balgobin]

Good day Bernie Deitrick
I finally got this to work, see code below.

Can you assist me with this I am at a brick wall here.

During the save "new quote" process there are two Save As Dialog;

1st: Is to save the new quote.
2nd: Is a save option for the new quote that has automatically opened in the background

Is there a way to force the 2nd process to complete in the background while not opening up the new quote automatically?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I have attached my test documents and screenshots.



Kind regards,
OB

[Bernie Deitrick]

Try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Omkar Balgobin]

I applied the change however the document crashes.

[Bernie Deitrick]

Create a workbook named "Serial Number.xlsx" and enter the last used serial number into cell A1 of the first sheet. Save it in the same folder as your workbook, then change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

And then link your serial number cell in your workbook to that workbook's A1.

[Omkar Balgobin]

I tried you approach but was unsuccessful, I have moved on to other elements in my template. I will return to this when I have complete the other element I am working on.

Thanks for your assistance thus far, I will dial back with you on this.
Kind regards,
OB