Let's say you have a tempalate file and you perform a lot of work on that tempalate and accidentaly save that template with all the changes. The original tempalate will be lost and you'll have a lot of work to do.
To avoid this you can save the tempalate file before closing it. But, "to err is human" and what if you forgot to do that.
Well, VBA is here for your rescue.
Using VBA you can ask the user to input the file name every time you close the file.

Assumptions:
User wants to save the file as ".xlsx".

Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. On the top dropdown menu select Workbook
4. In the second dropdown menu select BeforeClose
5. In the editor window enter the code below in between the two code lines that are there
The VBA editor window should like below image.

6. Close the VBA editor
7. Close the file and input the file name as shown in below image.

Code:
Application.DisplayAlerts = False
fName = Application.GetSaveAsFilename(filefilter:="Excel Files (*.xlsx), *.xlsx")
ActiveWorkbook.SaveAs Filename:=fName
Application.DisplayAlerts = True

Tip:
You can change the xlsx file filter as per your requirement e.g. .xls, .xlsm etc.
Multiple file filters can also be used separated by a comma (",") e.g. .xlsx, .xls.

I think this has been informative and I thank you for viewing.

-Saurabh