Macro to stop a Workbook being saved anywhere but its current location and name

[tonywatsonhelp]

Hi Everyone,

I need help, once again despite my best effort people have suppased my expected level of stupid.
Whats happening is they are opening a document then saving a copy in a different location.

I've gotten past the problem of them doing this in the folder by using a shortcut and saving the file in a location hidden deep. works great but now they are starting to save as on there desktop etc.

So here's all I need, lets no over complicate things

If Possible a macro that disables save as so they can only save, this would fix it.
any method is fine.

Please help if you can as its only Monday and I really don't want to end up on a murder charge when I get to the end of my tether with stupid!!!!!

Thanks

 

[RoryA]

Re: Macro to stop a Document being saved anywhere but its current location and name

In the Thisworkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then
        MsgBox "Please stop trying to save this elsewhere. Just use SAVE!!"
        Cancel = True
    End If
End Sub

Not foolproof, as nothing is.

 

[Yongle]

Re: Macro to stop a Document being saved anywhere but its current location and name

Put in ThisWorkbook code module (not a standard module)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then
            MsgBox "Save As is disabled", vbInformation
            Cancel = True
    End If
End Sub

But to alow you some flexibility, make an exception for yourself like this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then
        If LCase(Environ("username")) <> "yongle" Then
            MsgBox "Save As is disabled", vbInformation
            Cancel = True
        End If
    End If
End Sub

Notes
- Environ("username") may hold a different value to Application.UserName (use either)
- user names may be set up in an inconsistent manner - hence LCase

 

[Macropod]

Re: Macro to stop a Document being saved anywhere but its current location and name

You really can't ensure that via a macro as the users can simply use Windows to copy the workbook to another folder without even opening the workbook...

 

[njimack]

Re: Macro to stop a Document being saved anywhere but its current location and name

You really can't ensure that via a macro as the users can simply use Windows to copy the workbook to another folder without even opening the workbook...

Could you not use a WorkbookOpen event to check ThisWorkbook.FulName to display a msgbox and then delete the file?

 

[Macropod]

Re: Macro to stop a Document being saved anywhere but its current location and name

And if the user doesn't enable the macro?...

 

[njimack]

Re: Macro to stop a Document being saved anywhere but its current location and name

And if the user doesn't enable the macro?...

Then the file doesn't work. There are methods of forcing users to enable macros.

 

[Macropod]

Re: Macro to stop a Document being saved anywhere but its current location and name

Then the file doesn't work. There are methods of forcing users to enable macros.

None of them reliable...

 

[tonywatsonhelp]

Re: Macro to stop a Document being saved anywhere but its current location and name

Hi Everyone, Thank you all for your Advice,
Just for the record RoryA and Yondle, both great Ideas and will be perfect for what I'm trying to do.
Macropod, Appreciate you pointing out they can just copy the file but I have gotten around this with using a None Excel Shortcut to open the excel Document so even if they make a copy they are only coping a short cut so this works fine. It was once the document is opened that I have the problem.
The Idea of having a macro check the file path on opening the document is excellent and will be a great extra safety measure. all in all problem is now solved thank you all very much.
Tony

 

[Macropod]

Re: Macro to stop a Document being saved anywhere but its current location and name

HAppreciate you pointing out they can just copy the file but I have gotten around this with using a None Excel Shortcut to open the excel Document so even if they make a copy they are only coping a short cut so this works fine.

Very few users are likely to be fooled by that (shortcuts are pretty obvious), and those that are will only be fooled once...