Forum Discussion
Force a SAVEAS with a designated filename
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 SubInteresting 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
Mr. Chan,
You're absolutely right, I just found out that solution, it opens on the path of the workbook.
I also divert it to the sheet "RAD" if ever Cancel is pressed.
Now, there is one more problem...
How do I divert it to this procedure if the SAVE ICON is pressed or when the user chose SAVE?
I am thinking of making the file Read-only...
or is there another procedure?
many thanks
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim file_name As Variant
Dim FName As String
FName = Replace(ThisWorkbook.FullName, ".xlsm", "") & " for the month of " & _
Format(Sheets("RAD").Range("A2"), "mmmm-yyyy") & ".xlsm"
file_name = Application.GetSaveAsFilename(FName, _
FileFilter:="Excel Files,*.xlsm,All Files,*.*", Title:="Save As File Name")
If file_name = False Then
Cancel = true
Sheets("RAD").select
Exit Sub
End If
If LCase$(Right$(file_name, 5)) <> ".xlsm" Then
file_name = file_name & ".xlsm"
End If
ActiveWorkbook.SaveAs Filename:=file_name
End Sub
