SAVEAS a designated filename

[LFKim2018]

I had searched the net for SAVEAS and nothing come close to what I needed.

I have a workbook (a template) "RAD Analysis.xlsm" and in the sheet "RAD" cell A2 = 8/3/2018 and I want to force a SAVEAS filename as: "RAD Analysis for the month of August-2018.xlsm" - how do I write the vba for this - in workbook beforeclose or beforesave?

I declared nWB as ActiveWorkbook.name
ndate as date ** date in sheet RAD cell A2
set ndate = WorksheetFunction.text(ndate,"mmmmmmmm-yyyy") ** is this correct?
set nWB = WorksheetFunction.left(nWB,len(nWB)-5) ** getting the name without the ext .xlsm
set NewWBname = nWB & " for the month of " & Str(ndate) & ".xlsm"
here I am getting error message...

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.

many many many thanks

 

[steve the fish]

Here is how you could create the string if that helps:

NewWBname = Replace(ThisWorkbook.FullName, ".xlsm", "") & " for the month of " & Format(Sheets("RAD").Range("A2"), "mmmm-yyyy") & ".xlsm"

 

[LFKim2018]

Here is how you could create the string if that helps:

NewWBname = Replace(ThisWorkbook.FullName, ".xlsm", "") & " for the month of " & Format(Sheets("RAD").Range("A2"), "mmmm-yyyy") & ".xlsm"

Mr. Steve the fish
thank you for your reply..
what I wish to get is that this filename (NewWBname) to appear in the SAVEAS dialog box whenever the user press [x] or exit the program - thereby saving the file as different name (not overwriting the template).
maybe you can assist me in the codes for sub workbook before close or save routine?
this routine should bypass (if possible) the default save box of excel - as there might be two saving boxes appearing one after the other..
many many thanks

 

[LFKim2018]

NewWBname = Replace(ThisWorkbook.FullName, ".xlsm", "") & " for the month of " & Format(Sheets("RAD").Range("A2"), "mmmm-yyyy") & ".xlsm"

I tested your string above - IT WORKED NICELY!

 

[LFKim2018]

I managed to find an article and was able to come up with a SUB - but needs a little more tweaking....
How do I make the dialog box open at the active path?
many thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim file_name As Variant
    Dim FName As String
GB: '[I][B]**to loop back to dialog box if cancel is pressed[/B][/I]
    FName = Replace(ThisWorkbook.name, ".xlsm", "") & " for the month of " & _
            Format(Sheets("RAD").Range("A2"), "mmmm-yyyy") & ".xlsm"
' [U][I][B]** how do I make this dialog box open at the active path?[/B][/I][/U]
    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

 

[LFKim2018]

I found the solution by changing the FName = Replace(ThisWorkbook.FullName, ".xlsm", "")...
The only problem now is how to divert to this procedure if the user pressed the SAVE Icon or chose SAVE....
should I repeat the same code in Workbook_BeforeSave? as is or with some refinements?
many thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim file_name As Variant
    Dim FName As String
    FName = Replace(ThisWorkbook.[B]FullName[/B], ".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

[/QUOTE]

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...16-force-saveas-to-a-designated-filename.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[LFKim2018]

Cross posted https://www.excelforum.com/excel-pr...16-force-saveas-to-a-designated-filename.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

so sorry for violating a rule..
I posted same query to Microsoft TechCommunity and Excel help Forum.
In my other posts, whenever I get an answer- I always post it for others to see...
if this merits something..

 

[Fluff]

I posted same query to Microsoft TechCommunity

Please supply a link to the thread.

 

[LFKim2018]

Please supply a link to the thread.

https://techcommunity.microsoft.com...-with-a-designated-filename/m-p/224374#M16752