Excel Disable Save, while allowing VBA controlled Save As

[wiseaa1224]

Good Morning All,

I am working on a little problem. I have a workbook that serves as a starting templete for another workbook. The user inputs data onto this sheet and then a command button saves it to another location and keeps the original templete intact. I would like to prevent the user from saving the origial document, but still allow them to use the command button to save it to another location. I have found a few ways to do this. However, they only allow true "Save As", meaning going file-saveas. It does not seem to work for the vba command button. I would really appriciate your help with this problem.


aw

 

[par60056]

This may help but it may not. When the VBA does the saveAs, does the UI come up for them to name it?

If so you can use the worksheet event "BeforeSave" like this:

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

This checks if the UI for SaveAs will come up.

If you are not using the SaveAs UI, then it can be a little more complicated. Define a global variable in your code module like CanSave, in the workbook.open event set the global false, in the VBA for your button set it to true before the saveAs and test that in the beforesave event.

I have not tried this but it logically should work.

 

[wiseaa1224]

Thanks so much for the response. What you suggest coding wise does not work. However, your explaination makes sense and I'll just need to mess around with it and see if I can get some round-a-bout way to get it done. Thanks again.

 

[par60056]

What doesn't work?

Here I expanded the code.

In the workbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If canSave Then
    ' save called from button so should be ok
    Cancel = False
ElseIf (Not SaveAsUI) Then
    Cancel = True
    MsgBox "Save not allowed.  SaveAs a new file", vbOKOnly
End If


End Sub


Private Sub Workbook_Open()
canSave = False
End Sub

In your code for the button or macro they run:

Global canSave As Boolean


Sub myButton()
Dim wb As Workbook
Set wb = ActiveWorkbook
canSave = True


wb.SaveAs "newfile.xlsm", xlOpenXMLWorkbookMacroEnabled
End Sub

obviously in your code you will ask them for the name to saveAs and make sure it is not the same name as your template before saving. This will be trick when you want to change the template because you will have to do a saveas and then rename it.

 

[par60056]

obviously in your code you will ask them for the name to saveAs and make sure it is not the same name as your template before saving. This will be trick when you want to change the template because you will have to do a saveas and then rename it.

Or you can cheat when you make changes and go into the debugger and set canSave to true and then save