Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Macro to prevent 'save-as'

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there any way to write a macro which can prevent computer illiterate users from saving their files EVERYWHERE??

    Thanks,
    nica

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-21 06:58, nicademas wrote:
    Is there any way to write a macro which can prevent computer illiterate users from saving their files EVERYWHERE??

    Thanks,
    nica
    By EVERYWHERE, do you mean that the users just save the workbook in whatever directory/folder they happen upon? Or do you mean that you don't want the user to sve the workbook at all?

    To stop the user from saving the workbook at all you can do this:

    1. Right click on the XL sheet icon to the left of the "File" menu, choose "View Code".
    2. Copy and Paste this code:


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Me.Saved = True
    End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    End Sub

    That will stop them from saving (assuming that they don't turn off macros at startup).

    Forcing the user save in a specific directory is a bit more tricky. If this is what you want to do then let me know what the full path of the directory is and I can code this if you want. e.g. "C:TempMyImportantFiles"


    _________________
    Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-02-21 07:32 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need them to be only able to save - in the directory they open it from & not save-as becasue they tend to put the files in the strangest places & very valuable data is lost.

    Thanks so much!
    Nica

  4. #4
    New Member
    Join Date
    Oct 2006
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    please explain how to perform your first point - do not want them to be able to "save as" only "save." thanks

  5. #5
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nica,
    The easiest way would be to customezie each of the users toolbars and remove the saveas option and button. This would prevent "illiterate" users from saving everywhere.

    The first save of a file would be prompted for a folder, then they would only be able to overwrite the current file name.

    HTH
    Cal

    PS-I would recommend training the users vs. limiting thier access to functionality, so I hope you have a good reason.

  6. #6
    New Member
    Join Date
    Oct 2006
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    our agency is too large to do that, they create copies of the original file...isn't there a way to use macros to do something

  7. #7
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nica,
    If you are talking about a single file, and the user security is set to medium, this would be possible, but to limit all users save options for all files, it would have to be on a single PC basis.

    HTH
    Cal

  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I just got a great question via PM.

    "How do you add the code that I suggested to a workbook and then save it?"

    The easiest answer is to do the following:

    After you have made the code changes you need to make.

    1. Go to View|Toolbars and choose "Visual Basic"
    2. Toggle the 6th control from the left "Design Mode"
    3. Save your workbook
    4. Close and reopen the workbook. Design mode should be off.

  9. #9
    New Member
    Join Date
    Oct 2006
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    These codes works fine thanks.

    But how do you prevent users from taking the file (without opening it) and copy paste it to somewhere else?

  10. #10
    Board Regular
    Join Date
    Mar 2006
    Location
    Munich
    Posts
    262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    But is it still possible to save the workbook with a macro function?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •