Printable View
Hi,
I can disable the save option using below code in ThisWorkbook.
After I close the current excel sheet, the Macro code disappears when I open new excel sheet.
I tried applying breakpoint, but this is not user friendly.
Please get back soon for above query.
Code:Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub
Open the file while ensuring macros are disabled.
Write the code (exactly correctly) (best to paste it in from an earlier copy from say, notebook).
Save and close the file.
Open the file, allowing macros.
when I open the Excel, macros are disabled by default. I didn't get the option Open the file, allowing macros as you mentioned above.
The macro won't be there the next time when I open the excel.
Please let me know.
After you paste the code in, when you try and save the workbook what happens?
With the spreadsheet active (not the vbe) do alt+t+m+s.
I get:
Attachment 15185
but what do you see?
Another way, which will test whether macros are enabled, code in the right place etc.:
Put a break point at the first line of the BeforeSave macro (put your cursor on any one of the lines, including the very first line (but it has to be on or before the Cancel = True), and press F9 on the keyboard).
Save the file. At this point it should stop and highlight the line where it has stopped.
In the vbe, click the Reset button (a small square). At this point the file should save.
Remove the break point (or just close the file).
Test.
I enabled all macros from trust centres. And the adding breakpoint works.
The files saves as .xlsb and when ever I open excel "Save" option is disabled but "Save As" still allows me to save the file.
Below is the code I entered in VBA :
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "You can't save this workbook!"
Cancel = True
End Sub
I don't know why the Save option is disabled on an .xlsb file - have you been playing with disabling that feature elsewhere?
Do you get the MasgBox coming up?
A few links you should be able to sort things out with:
http://stackoverflow.com/questions/1...e-as-using-vba
http://www.mrexcel.com/forum/excel-q...acro-save.html
http://answers.microsoft.com/en-us/o...09?db=5&auth=1
I won't get any message, but "Save" and "Ctrl+s" options are disabled. I.e it won't allow me to save excel. Only Save As options allows me to save.
Strange behaviour from Excel.
Btw may I know any API to call this above MACRO from different tools, to be precise I want to call this MACRO from Visual Studio 2013 or from Qt to disable SAVE option in EXCEL.
If you get no msgbox coming up then the code is not being triggered and you are either stopping save etc. in some other way, or the code is not in the right place, that is the Thisworkbook code-module of the workbook you want this to work on.
Quote:
when ever I open excel "Save" option is disabled
Use Save As rather than Save