excel workbook: do not allow certain file name

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,809
Office Version
2019
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,809
Office Version
2019
Platform
Windows
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

VBA 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.
 
Last edited:

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,809
Office Version
2019
Platform
Windows
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

Well-known Member
Joined
Mar 26, 2009
Messages
1,809
Office Version
2019
Platform
Windows
the problem is now solved with a little trickery. I edited the following two lines of the code and voila...
VBA Code:
    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

Well-known Member
Joined
Dec 5, 2002
Messages
7,781
Office Version
2016
Platform
Windows
Woudn't something like this do what you want?

VBA Code:
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

Well-known Member
Joined
Mar 26, 2009
Messages
1,809
Office Version
2019
Platform
Windows
Woudn't something like this do what you want?

VBA Code:
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

Well-known Member
Joined
Mar 26, 2009
Messages
1,809
Office Version
2019
Platform
Windows
Woudn't something like this do what you want?

VBA Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,781
Office Version
2016
Platform
Windows
did you include the Repeat: clause at the top of the code I posted ?
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,809
Office Version
2019
Platform
Windows
Woudn't something like this do what you want?

VBA Code:
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
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,187
Messages
5,448,864
Members
405,535
Latest member
KLFT

This Week's Hot Topics

Top