+ Reply to Thread
Results 1 to 7 of 7

Excel: How to disable "traditional" Save and Save As functionality

  1. #1
    Registered User
    Join Date
    06-05-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Excel: How to disable "traditional" Save and Save As functionality

    Hi - I need to disable "traditional" Save and Save As (via ribbon, Ctrl+S, File Menu) in my Excel 2016 workbook, but allow users to ONLY save my workbook with a macro enabled button. The macro enabled button logic works fine by itself, but i want to limit users' save capabilities to just that button.

    Thanks!

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Excel: How to disable "traditional" Save and Save As functionality

    This is harder than you think.

    Select the developer tab
    Select view and then project explorer
    Double Click where it says "This Workbook"
    Paste this code in the module that opens and close it.

    Please Login or Register  to view this content.
    That is the easy bit.

    The question is how do you ensure that the user enables macros?

    You need to hide all worksheets but one and only let them be visible if macros are enabled.

    This will unhide all sheets

    Please Login or Register  to view this content.

    Your final problem is how to hide all sheets except the sheet named "Instructions".

    Select the developer tab
    Select view and then project explorer
    Select your project
    Select insert and then module
    Paste this code in the module that opens and close it.
    Select the developer tab
    Select Macros
    Select Test and then run.



    Please Login or Register  to view this content.
    Last edited by mehmetcik; 06-05-2018 at 06:47 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    06-05-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Re: Excel: How to disable "traditional" Save and Save As functionality

    Thank you for your response and feedback. Should the logic above be included into the "ThisWorkbook" object? Also, here is what I had originally created in my Workbook, and which didn't work as expected...prompting me to ask the experts on this forum:

    VBA to disable just the traditional "Save" functionality (saved in the "ThisWorkbook" object):

    Please Login or Register  to view this content.
    VBA for the "Save As" macro-enabled button (saved in a VBA Module):

    Please Login or Register  to view this content.

    The issue here is that the code to disable the traditional "Save" worked. The button however (using the VBA above) returns the message and "Save" restrictions that I would expect from a straight Save...and not a Save As. It's almost as if the 2 macros conflict with each other in some way.

    Thank you again for all of your help. Any advice to overcome these challenges is greatly appreciated!

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Excel: How to disable "traditional" Save and Save As functionality

    Your macro is inherently wrong.


    Please Login or Register  to view this content.
    Last edited by mehmetcik; 06-06-2018 at 03:52 PM.

  5. #5
    Registered User
    Join Date
    06-05-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Re: Excel: How to disable "traditional" Save and Save As functionality

    Thank you again for your help. I corrected the macro with your suggestion above, however, I still cannot get the Save As button to work with this macro. The macro you provided above works great with the Save on the ribbon, Ctrl+S, and Save/Save As via the "File" menu, but I still get the "You must use the 'Save As' button on the Summary tab to save this pricing workbook" message when I use the button. The macro I created for the button is as follows....I'm assuming there is at least 1 error that is causing the issue:

    Please Login or Register  to view this content.
    Thank you very much again for your expert advice on this. It will be great if this button can work as I'm hoping it can.

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Excel: How to disable "traditional" Save and Save As functionality

    Maybe you need to set a flag.

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-05-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Re: Excel: How to disable "traditional" Save and Save As functionality

    This worked great. Thank you very much for your advice and assistance over the past few days.

+ 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. 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
  2. [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
  3. 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
  4. Only "save" option in save prompt while closing excel sheet
    By rudswa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-30-2013, 08:07 PM
  5. Create a Command Button to "Save As" and "Close" an Excel Workbook
    By thedunna in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-26-2013, 05:38 PM
  6. Disable "Save As CSV" Warnings?
    By crowandazi in forum Excel General
    Replies: 1
    Last Post: 10-20-2007, 04:48 PM
  7. DISABLE "SAVE QUERY DEFINITION"
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-07-2005, 06:06 AM

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