CODEWITHSUNDEEP
excelvba
Craig
Craig

Reputation: 145

How to prevent my Save macro from running a second time

I have a file that many employees use to determine pricing.

When they're done, they change the file name to whatever they would like.

Leadership has asked that we make it to where the first part of the employee's file save name is the file's original name (so we know the version they're using) then the second part is what the user wants it to be. Ex - File is org called "Rate Calculator v14". Employee wants to name the file "Los Angeles, CA - John Doe".

The file should then save in a specific folder as "Rate Calculator v14 Los Angeles, CA - John Doe.xlsm".

This should happen regardless of Save or Save As and regardless of whether it's done with the Ctrl+s shortcut or use of the ribbon.

When the last line of code is fired, the file is in fact saved, but the macro loops back around and asks the user once again what they want to save the file as (though it now won't be saved and the other name they used already saved).

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim strFileName(0 To 2) As String

strFileName(0) = "M:\Sales\Rate Calculators\John\"
strFileName(1) = "Rate Calculator v14"
strFileName(2) = InputBox("What would you like to save the file as?")

ActiveWorkbook.SaveAs FileName:=Join(strFileName), FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

I'm aware I also need to build in some error handling for use of incorrect characters in a file name, file name length, and determine the user so their file is saved within the correct folder.

Upvotes: 1

Views: 60

Answers (2)

Craig
Craig

Reputation: 145

Answer:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim strFileName(0 To 1) As String, sPath As String
    Application.EnableEvents = False
    Cancel = True

    sPath = "M:\Sales\Rate Calculators\John"
    strFileName(0) = "Rate Calculator v14"
    strFileName(1) = InputBox("What would you like to save the file as?")

    ActiveWorkbook.SaveAs Filename:=sPath & "\" & Join(strFileName), FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Application.EnableEvents = True
End Sub

Upvotes: 1

Holly Golightly
Holly Golightly

Reputation: 41

Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim strFileName(0 To 1) As String, sPath As String

sPath = "M:\Sales\Rate Calculators\John\"
strFileName(0) = "Rate Calculator v14"
strFileName(1) = InputBox("What would you like to save the file as?")

ActiveWorkbook.SaveCopyAs Filename:=sPath & "\" & Join(strFileName) & ".xlsx"

End Sub

Upvotes: 1

Related Questions