Disable Save As

[DaveJohnson]

Hi I want to disable to Save As option I found the follwing which disables the whole file menu can this be change to disable a single item on the menu.

CommandBars("Worksheet Menu Bar").Controls("File").Enabled = False

Thanks.

 

[xlbo]

There are many different ways of saving so a different approach is going to work better - have a look at EVENTS - specifically the BEFORE_SAVE event where you should be able to put generic code to stop all saving activity

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi DaveJohnson,

Geoff is correct in as far as he goes.

My view on this is that you are wasting your time. What exactly do you want to achieve? Saving a copy from within Word is but one way to copy a document. This isn't even security by ignorance. If someone is trying to save a copy it is likely to be a deliberate action on their part and, when they find they can't do it through Word, they'll most likely do it through Windows - or cut and paste the content - or print to PDF - or ....

If you can arrange for the Users of your document not to have any access to writable media you might have a fighting chance but their computers would be virtually unusable in that case.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

Tony is corret if you are trying to stop users getting the data from a workbook - if you just want to make sure the numbers don't change then a better way may be to protect the workbook rather than try to prevent saving.....

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DaveJohnson]

I am using the BEFORE_SAVE event with the following code to save with a file name being the Journal ID

Dim NewJournal As String

NewJournal = Sheets("Map").Range("Journal_ID").Text

Application.Dialogs(xlDialogSaveAs).Show (NewJournal)

The only problem is if you use file save as it works but after the code has been run the save as come up again and you have to save twice.

Thanks

 

[TonyJollans]

Hi DaveJohnson,

Now I see! You're not trying to suppress SaveAs as a User action - you're trying to assist with the name used. If you put

Cancel=True

in your Before_Save Event, it will stop the system trying to do the SaveAs, whether or not you do anything else in the routine.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[DaveJohnson]

Thanks that works a treat.

one final question is there way to grey out the filename box so the user can only select the location but not change the filename offered.

Application.Dialogs(xlDialogSaveAs).Show (NewJournal)

I am sure I have seen this done before.

Thanks