Disabling SaveAs and Save?

[Petitboeuf]

Hiya guys!

I am looking for a way to disable SaveAs and Save in the File drop dow
menu as well as the prompt on Close event...

I have been rumaging around on the web without success...

Many thanks!

 

[Bob Phillips]

If you add this code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Not fSave Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

all saving should be disabled for that workbook. I have tested for fSave,
which if you create as a Public variable in a standard code module, you can
set that in your code and then save it. Remember to set back to false
aftwerwards.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Petitboeuf]

Bob

Thank you very much! It does work!!

Well... so much so that... how do i save my workbook with the module in
AFTER pasting it?!

 

[Bob Phillips]

As I mentioned, I added a variable to allow you to do that through code.

In a standard code module, declare the variable at the start

Public fSave As Boolean

Then add a simple save macro

Sub Private SaveMyWB()

fSave = True
ThisWorkbOkk.SaveAs "myFilename.xls" '<==== Change to suit
fSave = False

End Sub

I made it private so that it doesn't show in the list of macros from Excel.
Run it from the VBIDE by setting the cursor within the macro and the F5, or
call it in the immediate window.
--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Petitboeuf]

Bob

I am so sorry to bother you again but... well I'm no expert and still
trying real hard to understand that VB thingy

So far i have done the following:

1. in the ThisWorkbook code in General I have the Private Sub
Workbook_BeforeSave pasted

2. in the same location I have the Public Sub SaveMyWB pasted

3. I do not know where to put the Public fSave As Boolean as it doesn't
seem to stand alone...


Damn it's hard to be thick!

 

[Bob Phillips]

Put 2 and 3 in a standard code module (Insert>Module)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Petitboeuf]

Thank you very much.

Out of interest why does it work if in a Module instead of on a sheet

 

[Bob Phillips]

It probably would if you put everything in the worksheet module, including
the Public variable, but you would not be able to call it from the Excel
macro list.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)