| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disabling SaveAs and Save?
Hiya guys! I am looking for a way to disable SaveAs and Save in the File drop down menu as well as the prompt on Close event... I have been rumaging around on the web without success... Many thanks! J -- Petitboeuf ------------------------------------------------------------------------ Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602 View this thread: http://www.excelforum.com/showthread...hreadid=558792 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disabling SaveAs and Save?
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" wrote in message ... Hiya guys! I am looking for a way to disable SaveAs and Save in the File drop down menu as well as the prompt on Close event... I have been rumaging around on the web without success... Many thanks! J -- Petitboeuf ------------------------------------------------------------------------ Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602 View this thread: http://www.excelforum.com/showthread...hreadid=558792 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disabling SaveAs and Save?
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?! :) -- Petitboeuf ------------------------------------------------------------------------ Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602 View this thread: http://www.excelforum.com/showthread...hreadid=558792 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disabling SaveAs and Save?
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" wrote in message ... 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?! :) -- Petitboeuf ------------------------------------------------------------------------ Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602 View this thread: http://www.excelforum.com/showthread...hreadid=558792 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disabling SaveAs and Save?
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!  -- Petitboeuf ------------------------------------------------------------------------ Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602 View this thread: http://www.excelforum.com/showthread...hreadid=558792 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disabling SaveAs and Save?
Put 2 and 3 in a standard code module (InsertModule)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Petitboeuf" wrote in message ... 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! -- Petitboeuf ------------------------------------------------------------------------ Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602 View this thread: http://www.excelforum.com/showthread...hreadid=558792 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disabling SaveAs and Save?
Thank you very much. Out of interest why does it work if in a Module instead of on a sheet? -- Petitboeuf ------------------------------------------------------------------------ Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602 View this thread: http://www.excelforum.com/showthread...hreadid=558792 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disabling SaveAs and Save?
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) "Petitboeuf" wrote in message ... Thank you very much. Out of interest why does it work if in a Module instead of on a sheet? -- Petitboeuf ------------------------------------------------------------------------ Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602 View this thread: http://www.excelforum.com/showthread...hreadid=558792 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| QUERY: Excel's SaveAs so slow.. | Excel Worksheet Functions | |||
| Save as Msg box | Excel Discussion (Misc queries) | |||
| Save as Msg box | Excel Discussion (Misc queries) | |||
| File saves ok, SaveAs VERY slow | Excel Discussion (Misc queries) | |||
| Save and Saveas Greyed out on tool bar and file menu | Setting up and Configuration of Excel | |||
Linear Mode
