Forum Discussion
Force a SAVEAS with a designated filename
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 String
GB: '**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_name
End Sub
