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!

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

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

Thanks a lot Greg. What programming language is being used? I would like to learn.

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

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?

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

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.

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