Disabling SaveAs and Save?

  • Thread starter Thread starter Petitboeuf
  • Start date Start date
P

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!
 
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)
 
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?! :)
 
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)
 
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! :eek:
 
Put 2 and 3 in a standard code module (Insert>Module)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Thank you very much.

Out of interest why does it work if in a Module instead of on a sheet
 
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top