excel workbook: do not allow certain file name

[omairhe]

Hello all,


Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved is abc123.xlsx

A msgbox to notify user to change the filename, if this is the case.

Is it possible?
Thank you and will appreciate.

 

[Jaafar Tribak]

Take a look at Application.GetSaveAsFilename

 

[omairhe]

Take a look at Application.GetSaveAsFilename

Hello Jaafar,

It is good news because I too have a code with me that involves GetSaveAsFilename , I didn't know at first how to put in words exactly what my requirement was. Besides I couldn't find my requirement in any of the blogs that is to restrict specific filename.

To start over, and please bear with me, I want the filename to be different than the default filename which in this specific case is ="Monthly Sales Report - Month Year" Hence the user must change the default filename to something like ="Monthly Sales Report - June 2020" in order to save the workbook. By 'default' I mean the same which is output from the code below. Reason being, so that the user does not accidentally saves the file without giving the name of the month and the year first. the second benefit of doing this is to avoid overwriting the file with the same name in Desktop.
If its not much asking could you kindly help me out here please.
Any name other than the 'default' one should be acceptable, if not then msgbox user to give the file proper name.


my code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    Dim userResponce As String
    Dim ExternalLinks As Variant
    Dim x As Long
    Dim wb As Workbook
    Set wb = ActiveWorkbook

  
    userResponce = Application.GetSaveAsFilename(InitialFileName:="%USERPROFILE%\Desktop\Monthly Sales Report - Month Year", _
    fileFilter:="Excel Workbook(*.xlsx), *.xlsx")
    If userResponce = "False" Then
        Exit Sub
    Else
    wb.Unprotect "1234"
    wb.ActiveSheet.Unprotect "1234"
    ExternalLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(ExternalLinks) Then
    For x = 1 To UBound(ExternalLinks)
    wb.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
    Next x
    End If

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    wb.SaveAs userResponce, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    End If
End Sub

Thanks.

 

[omairhe]

I see this possible only with an AfterSave Event. the file would go through logical test if True then do nothing if False then delete the file and prompt user to rename the file and try saving again. False is when the file name is exactly "Monthly Sales Report - Month Year".

 

[omairhe]

the problem is now solved with a little trickery. I edited the following two lines of the code and voila...

    userResponce = Application.GetSaveAsFilename(InitialFileName:="%USERPROFILE%\Desktop\Local Sales Report - |", _
    fileFilter:="Excel Workbook(*. ), *")

The user will now be forced to give this a name other than the default one and will be prompted if he does not. Hence by introducing a special character at the end of filename was a fix in this case. though he would have to remove that special character by using a back space key, not much worrying there.

 

[Jaafar Tribak]

Woudn't something like this do what you want?

Repeat:

    userResponce = Application.GetSaveAsFilename(InitialFileName:="%USERPROFILE%\Desktop\Monthly Sales Report - Month Year", _
    fileFilter:="Excel Workbook(*.xlsx), *.xlsx")
    If userResponce = "False" Then
        Exit Sub
    Else
   
    If userResponce <> "abc123.xlsx" Then
        MsgBox "Wrong file name"
        GoTo Repeat
    End If

 

[omairhe]

Woudn't something like this do what you want?

Repeat:

    userResponce = Application.GetSaveAsFilename(InitialFileName:="%USERPROFILE%\Desktop\Monthly Sales Report - Month Year", _
    fileFilter:="Excel Workbook(*.xlsx), *.xlsx")
    If userResponce = "False" Then
        Exit Sub
    Else
 
    If userResponce <> "abc123.xlsx" Then
        MsgBox "Wrong file name"
        GoTo Repeat
    End If

Getting a compile error on hidden module but the idea seems right.. I'm using Excel 2019 64-bit if that would make any difference..

 

[omairhe]

Woudn't something like this do what you want?

Repeat:

    userResponce = Application.GetSaveAsFilename(InitialFileName:="%USERPROFILE%\Desktop\Monthly Sales Report - Month Year", _
    fileFilter:="Excel Workbook(*.xlsx), *.xlsx")
    If userResponce = "False" Then
        Exit Sub
    Else
  
    If userResponce <> "abc123.xlsx" Then
        MsgBox "Wrong file name"
        GoTo Repeat
    End If

Ok It seems to work and shows up the message box but when I add "Goto Repeat", I get a label not defined error.

 

[Jaafar Tribak]

did you include the Repeat: clause at the top of the code I posted ?

 

[omairhe]

Woudn't something like this do what you want?

Repeat:

    userResponce = Application.GetSaveAsFilename(InitialFileName:="%USERPROFILE%\Desktop\Monthly Sales Report - Month Year", _
    fileFilter:="Excel Workbook(*.xlsx), *.xlsx")
    If userResponce = "False" Then
        Exit Sub
    Else

    If userResponce <> "abc123.xlsx" Then
        MsgBox "Wrong file name"
        GoTo Repeat
    End If

Oh I had to put a "Repeat:" like this

Repeat:
    userResponce = Application.GetSaveAsFilename(InitialFileName:="%USERPROFILE%\Desktop\Local Sales - Month Year", _
    fileFilter:="Excel Workbook(*.xlsx), *.xlsx")

I guess am not an expert in vba yet, I do get by with expertise from helpful people like yourself.

Thank you Jaafar Tribak