+ Reply to Thread
Results 1 to 9 of 9

Disable save as only

  1. #1
    Registered User
    Join Date
    05-17-2006
    Posts
    13

    Disable save as only

    I have found multiple codes for disabling the save & save as options, but I would like to only disable 'save as' because I have multiple workbooks linked. Any help would be appreciated!

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,729
    Hi there,

    The following code will disable/enable the "Save As..." command on the main menu bar (File > Save As ...):

    Please Login or Register  to view this content.
    Hope this helps - please let me know how you get on.

    Best regards,

    Greg M

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,729
    Hi again,

    To prevent the "Save As ..." command from executing (as opposed to disabling the commandbar button for it), enter the following code in the "ThisWorkbook" VBA module of your workbook. This will prevent users from accessing the command via a toolbar other than the main menu bar.

    Please Login or Register  to view this content.
    Hope this helps - please let me know how you get on.

    Best regards,

    Greg M

  4. #4
    Registered User
    Join Date
    05-17-2006
    Posts
    13
    Thanks a lot Greg. What programming language is being used? I would like to learn.

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,729
    Hi again Cajun,

    Many thanks for the feedback. You're welcome - delighted to have helped.

    This code is Excel VBA (Visual Basic for Applications). It's quite powerful - just about anything you can do "by hand" can be automated using VBA, so try using it to do something very simple (e.g. adding a worksheet to a workbook) and take things from there.

    All the best & happy programming,

    Greg M

  6. #6
    Registered User
    Join Date
    05-17-2006
    Posts
    13
    Greg (or anyone else)
    I ran into another problem. Once I reopen the file, I get the option to enable or disable macros on workbook. If I choose disable macros, then the save as funtion can be used. How can I get the workbook to automatically open with the macros enabled?

  7. #7
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,729
    Hi again,

    Ok - there's no accounting for the deviousness of users!!!

    This one can be solved by a work-around. When the workbook opens with macros disabled, the only worksheet visible will be one which says "Sorry - this workbook can be used only with macros enabled". The workBOOK is password-protected, so no access to the other worksheets is possible. Users CAN use the "Save As" command, but it's rather pointless to do so as the same restrictions will apply to any copies of the workbook.

    When the workbook is opened with macros enabled, a macro will automatically hide the "Sorry" worksheet & reveal all of the others.

    To achieve the worksheet hiding described above, a macro is used to hide the "normal" worksheets and reveal the "Sorry" worksheet immediately before the workbook is saved. As soon as the "Save" operation is completed, the macro reveals the "normal" worksheets and hides the "Sorry" worksheet - but this configuration is NOT saved. This ensures that the saved version (i.e. the one which users will subsequently open) will display only the "Sorry" worksheet, unless macros are enabled.

    The code for this one is a bit more complicated than the original, so I've attached a workbook with the necessary code included.

    Hope this helps - please let me know how you get on.

    Best regards,

    Greg M
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-17-2006
    Posts
    13
    Greg, you are the MAN!!! I thought I was advanced in Excel until reading these forums and them I realized how 'Forrest Gump" I was. Thanks again for all your help.

  9. #9
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,729
    Hi again Cajun,

    Once again, many thanks for the feedback. You're very welcome - I'm delighted that I was able to help out.

    All the best,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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