Aug 4th, 2010, 01:13 PM
VBA disable save. Enable save as & save with vba macro
I want to prevent users from using the "Save" function for my workbook.
I want to allow 2 options for saving: "Save As" & VB script.
Currently, they can save using a button linked to the following script:
Range("C3").Value = Range("J12").Value
Range("C11").Value = Range("J10").Value
SaveName = Range("C3").Value
SaveLocation = Range("J7").Value
ActiveWorkbook.SaveAs Filename:=SaveLocation & SaveName & ".xls"
I also have some script to disable saving, but it conflicts with the above script.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True
MsgBox "You cannot save this workbook. Use Save As"
How can I modify the 2nd bit of code so that it won't interfere with the first?
Aug 4th, 2010, 01:53 PM
Aug 4th, 2010, 02:39 PM
Re: VBA disable save. Enable save as & save with vba macro
Thank you Koolsid, for the warm welcome and quick response.
However I think my situation is slightly different..
Users must be able to save using a button that links to my save code (the first block of code). That button saves the file and automatically names it based on some cell values. This code never opens the SaveAs GUI. Which means that this method of saving is shut down by the 2nd block of code (which cancels the save if the SaveAs GUI is not TRUE.).
Am I correct? If so, do you know if there are any other possibilities? By the way, I can't use the "read-only" approach. At my office I'm unable to set permissions. So I really need a VBA approach.
Click Here to Expand Forum to Full Width