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]

Glad you got this working in the end and thanks for the feedback.

 

[omairhe]

Glad you got this working in the end and thanks for the feedback.

Oh I thoroughly checked the code and it will give me a MsgBox in every instance no matter what I named the file with. What am I be doing wrong here?

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 <> "Monthly Sales Report - month year" Then
        MsgBox "Please add month and year to the filename"
        GoTo Repeat
        End If

 

[Jaafar Tribak]

What is the file name that is not permitted ?

 

[omairhe]

What is the file name that is not permitted ?

Monthly Sales Report - May 2020
not permitting the above.

 

[Jaafar Tribak]

Monthly Sales Report - May 2020
not permitting the above.

in that case this should work :

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

 

[omairhe]

in that case this should work :

Repeat:

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

Yes but all filenames are permitted now. the logical test is either accepting all or rejecting all with = and <> respectively

 

[Jaafar Tribak]

Yes but all filenames are permitted now. the logical test is either accepting all or rejecting all with = and <> respectively

Maybe I didn't understand... In post#14 you said that "Monthly Sales Report - May 2020 " is the file name that is not permitted .. So if
userResponce = "Monthly Sales Report - May 2020" then display msgBox and display the save dialog again.

 

[Jaafar Tribak]

Sorry I overlooked the file extension

Try this variation :

Repeat:
 
    userResponce = Application.GetSaveAsFilename(InitialFileName:="Monthly Sales Report - May 2020", _
    fileFilter:="Excel Workbook(*.xlsx), *.xlsx")
    If userResponce = "False" Then
        Exit Sub
    Else
    
    If InStr(1, userResponce, "Monthly Sales Report - May 2020", vbTextCompare) Then
        MsgBox "Wrong file name"
        GoTo Repeat
    End If

 

[omairhe]

Sorry I overlooked the file extension

Try this variation :

Repeat:

    userResponce = Application.GetSaveAsFilename(InitialFileName:="Monthly Sales Report - May 2020", _
    fileFilter:="Excel Workbook(*.xlsx), *.xlsx")
    If userResponce = "False" Then
        Exit Sub
    Else
   
    If InStr(1, userResponce, "Monthly Sales Report - May 2020", vbTextCompare) Then
        MsgBox "Wrong file name"
        GoTo Repeat
    End If

OKi it is closer now. But there 2 problems.
1. the default location is not set to desktop
2. the file with name "Monthly Sales Report - May 2020 Sunday" will still be rejected even though it has changed.

for the second part if the user removes 2020 and then add sunday this will work
"Monthly Sales Report - May Sunday"

Could it be also taken care of please.

 

[Jaafar Tribak]

For question 1, just change the initial filename to this "%USERPROFILE%\Desktop\Monthly Sales Report - month year".

As for your second question, I am getting confused and I am not sure I understand what is supposed to happen. Can you explain what the scenario is ?