+ Reply to Thread
Results 1 to 16 of 16

Disable Save & Save as even when users click to "break" code

  1. #1
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    155

    Disable Save & Save as even when users click to "break" code

    Hi everyone,

    I'm looking to build a spreadsheet that cannot be Saved / Saved As by the user at all. Looking online, I've seen the following piece of code which seems to work at first:
    Please Login or Register  to view this content.
    When VBA is running, the code does its job. My issue is that nothing prevents the user from accessing the VBA window and clicking the "break" button to temporarily stop the subs from running, then saving the document and reactiving the subs afterwards. Is there a way around that trick?

    Thanks

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Disable Save & Save as even when users click to "break" code

    Hi, yes there is.
    You disable the Break key and the Esc key.
    And of course you don't add a Msgbox in events.
    This halts the macro so te user can press Ctrl Break at that moment.
    No Message boxes, place a notification in the StatusBar, that's enough
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    155

    Re: Disable Save & Save as even when users click to "break" code

    Thanks Keebellah. I think I'm missing something here. Are you suggesting that I deactivate the Esc key and break key at all time when using the spreadsheet? (because I dont think that is recommended right?)

    Because to me, the issue is not really to avoid the user from stopping the sub while it's running (although it's also another issue), it's more that the user can, at any point in time when using the spreadsheet (not just when saving), pause the whole vba of the spreadsheet and then save it normally.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Disable Save & Save as even when users click to "break" code

    Then you should remove the message box in the workbook save event

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Disable Save & Save as even when users click to "break" code

    You can password protect the VBA project, which would prevent them from getting to the code, but if they disable macros, then you're back to square one.

  6. #6
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    155

    Re: Disable Save & Save as even when users click to "break" code

    Even with a password on the VBA project, at any time, a user can press Alt+F11 and click on Attachment 677012 and stop all macros currently ongoing (therefore enabling save and save as), or am I missing something?

    Therefore, what is even the point of this vba code anyway?

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Disable Save & Save as even when users click to "break" code

    You're attachment is invalid, so I cannot see what you are clicking.
    Any VBA can be bypassed, by simply preventing macros from being run by changing the macro security level.

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Disable Save & Save as even when users click to "break" code

    Without VBA you cannot even code what you want.

  9. #9
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    155

    Re: Disable Save & Save as even when users click to "break" code

    Understood. So there's really not much that can be done to prevent users from clicking || (break / pause) in the VBA window to disable the anti-save parade.

    Thanks

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Disable Save & Save as even when users click to "break" code

    Yes, you can lock the VBA project, that way they can't get into the code to set a break point.

  11. #11
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    155

    Re: Disable Save & Save as even when users click to "break" code

    But you don't need to enter the VBA project itself to click the pause / break button in the VBA window and making our piece of code ineffective. So locking it won't change anything (or I'm misunderstanding your statement)

    Thanks

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Disable Save & Save as even when users click to "break" code

    I thought you were talking about setting a break point in the code, which they couldn't do.
    But there is nothing to stop them from preventing the code from running.

  13. #13
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    155

    Re: Disable Save & Save as even when users click to "break" code

    No indeed, I'm trying to see if there is a way to really make the anti-save vba effective. But it seems like, in that regard, this measure would only fool complete neophytes in excel (that are not aware of VBA in general).

  14. #14
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Disable Save & Save as even when users click to "break" code

    There is no way of making it foolproof, even if somebody knows nothing about Excel/VBA if their macro settings are set to prevent macros from running, then nothing will work.

  15. #15
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    155

    Re: Disable Save & Save as even when users click to "break" code

    True, but in that regard, my sheets are in Veryhidden until macros are enabled so at least I know vba will be running initially (until someone pauses it)

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Disable Save & Save as even when users click to "break" code

    This is what I meant

    The VBA project is not protected and yes, if macro's are disabled then the user can save it an all that.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-09-2019, 02:15 PM
  2. VBA to click the "Open" or "Save" button in Internet Explorer Dialogue Box
    By kimvyp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2019, 12:22 AM
  3. [SOLVED] Excel: How to disable "traditional" Save and Save As functionality
    By JAMM302 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-07-2018, 12:08 PM
  4. Code only runs when a user hits "Save", not "Save As"
    By cjstewart8 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2014, 07:41 PM
  5. Replies: 4
    Last Post: 04-05-2014, 08:53 AM
  6. [SOLVED] Disable save and prevent "Do you want to save" message from prompting for read-only file
    By hcyeap in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-12-2014, 12:29 AM
  7. Need macro to disable "Save" and "Save as" buttons
    By Anforya in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-20-2013, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1