excel workbook: do not allow certain file name

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,781
Office Version
2016
Platform
Windows
Glad you got this working in the end and thanks for the feedback.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

omairhe

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

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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,781
Office Version
2016
Platform
Windows
Monthly Sales Report - May 2020
not permitting the above.
in that case this should work :
VBA Code:
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

Well-known Member
Joined
Mar 26, 2009
Messages
1,809
Office Version
2019
Platform
Windows
in that case this should work :
VBA Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,781
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,781
Office Version
2016
Platform
Windows
Sorry I overlooked the file extension

Try this variation :
VBA Code:
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

Well-known Member
Joined
Mar 26, 2009
Messages
1,809
Office Version
2019
Platform
Windows
Sorry I overlooked the file extension

Try this variation :
VBA Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,781
Office Version
2016
Platform
Windows
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,096,186
Messages
5,448,855
Members
405,533
Latest member
Heretical1

This Week's Hot Topics

Top