Save As Restrictions

jrisebo

Active Member
Joined
Mar 15, 2011
Messages
264
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
61,028
Office Version
  1. 365
Platform
  1. Windows
You can use VBA, specifically a "Before Save" event procedure script to ensure that they do not save the file to the specified location.
Of course, the caveat there is that VBA needs to be enable in order for that code to fire (so if they do not enable VBA code on their computer, the code will never run).

What I have seen people to is also add "On Open" and "On Close" VBA code, so that the data/worksheets are hidden, unless they enable VBA.
So they try to render the workbook unusable unless they opt to enable VBA.
 

jrisebo

Active Member
Joined
Mar 15, 2011
Messages
264
Office Version
  1. 365
Platform
  1. Windows
Those using the files have VBA enabled. The spreadsheet has a button to do a save as to the network folder based on project number, but sometimes people just open it, change ****, then hit save. I want it to not be allowed to be saved to the originating folder. So code to not let it save to a specfic folder, any other folder I could care less how often they save.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
61,028
Office Version
  1. 365
Platform
  1. Windows
I thought about that. I want it to be 100% that the file cannot be saved in the originating folder, but once saved somewhere else, then they can save as much as they want.
So what is the concern with using that method then?
Provided that they have enabled VBA, and saving it from that Excel file, that should be fairly simple to do.
 

jrisebo

Active Member
Joined
Mar 15, 2011
Messages
264
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So what is the concern with using that method then?
Provided that they have enabled VBA, and saving it from that Excel file, that should be fairly simple to do.
What happens if they just click 'yes'?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
61,028
Office Version
  1. 365
Platform
  1. Windows
That link I provided does NOT have the exact that you need - it just shows you how the BeforeClose event works.
Your code would check the file path that they are trying to save to, and if it is the one you do not want them to save to, you would set "Cancel = True", which cancels the save attempt.
 

jrisebo

Active Member
Joined
Mar 15, 2011
Messages
264
Office Version
  1. 365
Platform
  1. Windows
That link I provided does NOT have the exact that you need - it just shows you how the BeforeClose event works.
Your code would check the file path that they are trying to save to, and if it is the one you do not want them to save to, you would set "Cancel = True", which cancels the save attempt.
Ah, ok. Got it. Hopefully someone can help me with the code.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
61,028
Office Version
  1. 365
Platform
  1. Windows
Sorry, I thought when you mentioned that the project already had VBA code, that you might be able to write the code.
Here is some code. You would just need to change the value of "myDir".
VBA 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:\Temp\"
    
'   Get file save name
    fname = Application.GetSaveAsFilename
    
'   Check to see if they are trying to save to restricted directory
    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
    
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,822
Messages
5,772,469
Members
425,760
Latest member
zj042060

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top