Save As Restrictions

[jrisebo]

We keep our files in a shared network folder. Users can open the spreadsheet, and then should save as to a separate location. Our IT department wont restrict the folder to read only, so anyone can overwrite the files inside it. I want to eliminate one way to do that by having code in the file not allowing the spreadsheet be saved in the folder. Anyway to do that?

 

[jrisebo]

OK, I will ask one more time. Please post your version of my code that you are using, in its entirety.
The behavior doesn't seem quite right to me, so I want to make sure that you have everything in the correct place in the code.

I already did once. You copied some of the code.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim fname As String
Dim myDir As String

' Specify the folder path they CANNOT save to
myDir = "C:\Excel Files\"

' Get file save name
fname = Application.GetSaveAsFilename

' Check to see if they are trying to save to restricted directory
MsgBox myDir & vbCrLf & Left(fname, Len(myDir))
If UCase(Left(fname, Len(myDir))) = UCase(myDir) Then
Cancel = True
MsgBox "You cannot save file to " & myDir & " directory.", vbOKOnly, "ERROR!"
Else
MsgBox "File Saved"
End If

 

[Joe4]

I already did once. You copied some of the code.

I had asked for it after I asked you to add the MsgBox line, because it is not making sense that the second part of the Message Box is returning "False". It should be returning some values.
I wanted to make sure that you had the MsgBox in the correct place, which it appears that you do.

I cannot seem to reproduce the behavior you are describing. The code works for me.
Can you do a screen print of the save button you are clicking? I want to make sure that we are doing the same thing.

 

[jrisebo]

I had asked for it after I asked you to add the MsgBox line, because it is not making sense that the second part of the Message Box is returning "False". It should be returning some values.
I wanted to make sure that you had the MsgBox in the correct place, which it appears that you do.

I cannot seem to reproduce the behavior you are describing. The code works for me.
Can you do a screen print of the save button you are clicking? I want to make sure that we are doing the same thing.

Here is the button I press:

Regardless, If I press that or do File:Save it pops up this SaveAs dialog box:

Which makes sense, as we dont want it to save, we want it to Save As. But this is not the normal dialog box (you cant pick what type of excel File)

 

[Joe4]

OK, this isn't quite a simple as I thought, as I think we need to account for both the "Save" and "SaveAs" options.
Let me play around with it and see what I can come up with.

 

[jrisebo]

OK, this isn't quite a simple as I thought, as I think we need to account for both the "Save" and "SaveAs" options.
Let me play around with it and see what I can come up with.

Thanks. Yea, if they hit either one, Its desired that the file not be able to be saved in that directory.

 

[Joe4]

One other thought. I am not sure if this will satisfy your need, but what about making the file itself read-only?
They will still be able to them save copies to that network directory, but they won't be able to overwrite the ORIGINAL file?
Would something like that work?

 

[Joe4]

OK, with a little help with some friends, and borrowing some logic from this link here: How to force users to save as a macro enabled workbook?, I think I found something that works. It cancels the original save and creates its own prompt, where we can always capture the file folder.

See how it works for you:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim xFileName As String
    Dim myDir As String

'   Specify the folder path they CANNOT save to
    myDir = "C:\Excel Files\"
    
'   Cancel original save and prompt for save
    Cancel = True
    xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file")
    
'   Check to see if file name is restricted folder
    If UCase(Left(xFileName, Len(myDir))) = UCase(myDir) Then
        MsgBox "You cannot save file to " & myDir & " directory.", vbOKOnly, "TRY AGAIN!"
        Cancel = True
        Exit Sub
    End If
        
'   Save file if save was not cancelled
    If xFileName <> "False" Then
        Application.EnableEvents = False
        ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Application.EnableEvents = True
    Else
        MsgBox "Action Cancelled"
        Cancel = True
        Exit Sub
    End If
    
End Sub

 

[jrisebo]

OK, with a little help with some friends, and borrowing some logic from this link here: How to force users to save as a macro enabled workbook?, I think I found something that works. It cancels the original save and creates its own prompt, where we can always capture the file folder.

See how it works for you:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim xFileName As String
    Dim myDir As String

'   Specify the folder path they CANNOT save to
    myDir = "C:\Excel Files\"
   
'   Cancel original save and prompt for save
    Cancel = True
    xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file")
   
'   Check to see if file name is restricted folder
    If UCase(Left(xFileName, Len(myDir))) = UCase(myDir) Then
        MsgBox "You cannot save file to " & myDir & " directory.", vbOKOnly, "TRY AGAIN!"
        Cancel = True
        Exit Sub
    End If
       
'   Save file if save was not cancelled
    If xFileName <> "False" Then
        Application.EnableEvents = False
        ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Application.EnableEvents = True
    Else
        MsgBox "Action Cancelled"
        Cancel = True
        Exit Sub
    End If
   
End Sub

Thanks, will try and get to it later today and let you know how it works!

 

[jrisebo]

Ok, this works great, just as asked for.

One issue though is we do have a macro button in the spreadsheet that also does a Save As and rename to another file path, based on a cell value we type in.

i.e. - If cell value is 1000, then it saves it to a separate folder location in folder 1000. If cell value is 2000, it does a save as to folder 2000, etc. It does this automatically without opening a dialog box, since the path is predetermined based on cell value.

Is there a way to modify that specific macro to ignore the code you provided if the macro button is pressed? Currently the code you provided interferes also with our macro button.

Thanks

 

[Joe4]

Please post the VBA code you have for that button.