Hi,
I have a quotation form that will be used by multiple users and I am looking for a way to increase the quote number by 1 each time it is opened. I want the user to be able to create a new quotation and save it, but without saving over the master file.
Template - Quotation.xlsx
1) To open a file and not really be opening the original file, save it as an xltm (macro enabled template). Quotation.xltm
2) Your users must create a shortcut link to the template. When they open that file via this shortcut, Excel automatically creates a copy of the file, forcing the user to save it with a unique name and leaving the original file intact.
3) The template will have a Workbook_Open event that triggers when it is opened to check if the QUOTE number cell has been filled in, this is done by using a specific cell as a check cell, for instance cell AA1 must be empty for the macro to run. New quotes will be empty in AA1, so it will then secretly open the original template in the background and increment the number for the next user.
The entire method is demonstrated here. The sample file on that page is an xlt for Excel 2003, use xltm for 2007 and beyond.
Last edited by JBeaucaire; 12-26-2019 at 04:06 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thank you! I will try this out
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks