Excel Disable Save, while allowing VBA controlled Save As

wiseaa1224

New Member
Joined
Apr 18, 2014
Messages
10
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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:

Code:
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.
 
Upvote 0
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.
 
Upvote 0
What doesn't work?

Here I expanded the code.

In the workbook module:

Code:
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:
Code:
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top