golden_maze
New Member
- Joined
- Aug 29, 2020
- Messages
- 4
- Office Version
- 2016
- Platform
- Windows
Hi there,
I have a template file that I often use as a starting point for new projects. More often than not I start working on the file and habitually save my progress and therefore on accident overwrite the template. Very annoying.
As a solution, I want to write a macro that initiates "save as" and assign this macro the ctrl + s shortcut such that I can't save over my template on accident because Ctrl+s no longer saves but rather starts the "save as" macro. The newly created file should then no longer have this macro and I therefore want to save it as a .xlsx file.
I only have one problem: once the new file was saved it is indeed a .xlsx file but I need to close the Excel and reopen it so that the macro actually disappears. If after "saving as" I just keep working and press Ctrl+s to (regular) save, it still initiates the macro which shouldn't be in the file anymore because the file is .xlsx. I tried to get around it by some code I found which seems to "simulate" to reopen the file but it doesn't help.
The below is my code:
Help on this would be greatly appreciated! If there is another way to disable the macro once it has been used once for example then I would be open to that as well.
Thanks,
Marvin
I have a template file that I often use as a starting point for new projects. More often than not I start working on the file and habitually save my progress and therefore on accident overwrite the template. Very annoying.
As a solution, I want to write a macro that initiates "save as" and assign this macro the ctrl + s shortcut such that I can't save over my template on accident because Ctrl+s no longer saves but rather starts the "save as" macro. The newly created file should then no longer have this macro and I therefore want to save it as a .xlsx file.
I only have one problem: once the new file was saved it is indeed a .xlsx file but I need to close the Excel and reopen it so that the macro actually disappears. If after "saving as" I just keep working and press Ctrl+s to (regular) save, it still initiates the macro which shouldn't be in the file anymore because the file is .xlsx. I tried to get around it by some code I found which seems to "simulate" to reopen the file but it doesn't help.
The below is my code:
VBA Code:
Sub SaveAs()
Dim sFileSaveName As Variant
Dim myNewSheet As Worksheet
Set myNewSheet = ActiveSheet
sFileSaveName = Application.GetSaveAsFilename (Format (Now, "dd.mm.yyyy") & "_XXX_v01.xlsx", "Excel Files (*.xlsx), *.xlsx")
if sFileSaveName <> False Then
myNewSheet.Saveas fileName:=sFileSaveName, FileFormat :=51
End If
ThisWorkbook.Saved = True
ThisWorkbook.ChangeFileAccess xlReadOnly, , False
Application.Wait Now + TimeValue("00:00:01")
ThisWorkbook.ChangeFileAccess xlReadWrute, , True
End Sub
Help on this would be greatly appreciated! If there is another way to disable the macro once it has been used once for example then I would be open to that as well.
Thanks,
Marvin