I have a workbook named "RAD Analysis.xlsm" and I want to force a SAVEAS with a filename as:"RAD Analysis for the month of August-2018.xlsm" ActiveWorkbook.Name = "RAD Analysis.xlsm" ** (Read-only) fileSheets("RAD").Range("A2") = 8/3/2018 variable declaration - something like... Dim nWB = ActiveWorkbook.Name ndate = Sheets("RAD").Range("A2") newWBname = WorksheetFunction.left(nWB,len(nWB)-5) & Str(Text(ndate,"mmmmmmmm-yyyy)) I keep getting error on this part. How do I write the vba for this SAVEAS in the workbook_beforeclose or beforesave.. many many thanks

NewWBname = Replace(ThisWorkbook.name, ".xlsm", "") & " for the month of " & _Format(Sheets("RAD").Range("A2"), "mmmm-yyyy") & ".xlsm"courtesy of Mr. steve the fish

The procedure I would like to request is:Whenever the user click [X] at the top right or exit the workbook (if there are changes made),there should appear a saveas dialog box wherein the default is the above NewWBname (.xlsm)If the user tried to use the original name "RAD Analysis" - it should loop again for the default filename.this procedure should suppress (if possible) the default save dialog box of excel otherwise there might be two save boxes appearing one after the other..many many thanks

The procedure I would like to request is:Whenever the user click [X] at the top right or exit the workbook (AND there are changes made), there should appear a SAVEAS Dialog Box wherein the default is the above NewWBname (.xlsm)If the user try to SAVE AS the original name "RAD Analysis" - it should loop again for the default filename.this procedure should suppress (if possible) the default save dialog box of excel otherwise there might be two save boxes appearing one after the other..many many thanks

Kindly see codes below - just a little more tweaking...The default name is solved , How do I make the dialog box open at the active path?many many thanks Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim file_name As Variant Dim FName As StringGB: '**to loop back to dialog box if cancel is pressed FName = Replace(ThisWorkbook.name, ".xlsm", "") & " for the month of " & _ Format(Sheets("RAD").Range("A2"), "mmmm-yyyy") & ".xlsm"' ** how do I make this dialog box open at the active path? file_name = Application.GetSaveAsFilename(FName, _ FileFilter:="Excel Files,*.xlsm,All Files,*.*", Title:="Save As File Name") If file_name = False Then GoTo GB End If If LCase$(Right$(file_name, 5)) <> ".xlsm" Then file_name = file_name & ".xlsm" End If ActiveWorkbook.SaveAs Filename:=file_nameEnd Sub

Interesting code. Thanks very much. For your question, you may just consider FName as the full name. For example, you want to save at C:\Bakcup and the file name is "RAD Today.xlsm", then your FName should be "C:\Backup\RAD Today.xlsm". In that case, the default path for the save-as box will be C:\Backup

Force a SAVEAS with a designated filename

Lorenzo Kim
Bronze Contributor
Aug 03, 2018
NewWBname = Replace(ThisWorkbook.name, ".xlsm", "") & " for the month of " & _
Format(Sheets("RAD").Range("A2"), "mmmm-yyyy") & ".xlsm"
courtesy of Mr. steve the fish
- Lorenzo Kim
  Bronze Contributor
  Aug 03, 2018
  The procedure I would like to request is:
  Whenever the user click [X] at the top right or exit the workbook (if there are changes made),
  there should appear a saveas dialog box wherein the default is the above NewWBname (.xlsm)
  If the user tried to use the original name "RAD Analysis" - it should loop again for the default filename.
  this procedure should suppress (if possible) the default save dialog box of excel otherwise there might be two save boxes appearing one after the other..
  many many thanks
  - Lorenzo Kim
    Bronze Contributor
    Aug 04, 2018
    The procedure I would like to request is:
    Whenever the user click [X] at the top right or exit the workbook (AND there are changes made), there should appear a SAVEAS Dialog Box wherein the default is the above NewWBname (.xlsm)
    If the user try to SAVE AS the original name "RAD Analysis" - it should loop again for the default filename.
    this procedure should suppress (if possible) the default save dialog box of excel otherwise there might be two save boxes appearing one after the other..
    many many thanks

Forum Discussion

Force a SAVEAS with a designated filename

Share

Resources