Prevent Save and Save as unless using Macro Button

[billfinn]

Good afternoon!
I am trying to set up a Workbook so that using Save or Save As is disabled but Macro buttons that do specific Save As commands are allowed.
I have the following code which works well at disabling Save and Save As, but it won't allow macros that are tied to buttons to perform a Save As operation.
Full Disclosure, I posted a similar question on excelforum.com a few weeks ago but wasn't able to resolve the issue.
This is the code I am using. This code is in ThisworkBook.
[CODE
]Private Sub Workbook_BeforeClose(Cancel as Boolean)
ThisWorkbook.saved=True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUi as Boolean, Cancel as Boolean)
Cancel=True
End Sub
[/CODE]
I tried adding this same code to the top of my "save as" macro but change True to False in both places. It allowed me to save the file using the macro buttons but also allowed Save and Save As to work normally
Any suggestions or thoughts or brickbats are welcome. I am stuck.
Thanks much!
Bill

 

[offthelip]

you can try codel ike this:

Public inhibitsave

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If inhibitsave Then
ThisWorkbook.Saved = True
End If
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
If inhibitsave Then
Cancel = True
End If
End Sub


Private Sub Workbook_Open()
inhibitsave = True
End Sub


Sub allowsave()
inhibitsave = False
End Sub

 

[billfinn]

Offthelip,
Thanks much for the response! I'm working out where to place each of these little subs in my macro and then I will let you know how it works. Might be tomorrow if people keep bugging me.
thanks
Bill

 

[offthelip]

Put all the code in the "thisworkbook" object, then you put the "call thisworkbook.allowsave" in the macro you call from the buttons

 

[billfinn]

Offthelip,
Thanks very much! That code works exactly as I needed it to.
This gives me confidence that no one is going to screw up the main project file, they can only save using the macro buttons which save as different names in different folders.
I appreciate it very much!!
Bill

 

[billfinn]

Offthelip,
I did notice one odd thing. When I did the save using the macro button, I was then able to use Save or Save As with no issue. If I exited and reopened the workbook it was once again protected.
l tried calling ThisWorkbook.Workbook_Open after the save and before end sub but I was still able to save normally. Am I missing a trick?
Thanks much!
Bill

 

[offthelip]

You need create another sub in the workbook object because workbook_open is private sub and so it can't be called from outside the workbook object so add this sub:

Sub inhibitsave()
inhibitsave = TRUE
End Sub

and add the call:

call thisworkbook.inhibitsave

after you have saved using the macro

 

[billfinn]

Offthelip,
I had to change the new sub from inhibitsave to preventsave as there was a conflict with the Public inhibitsave but it works great!
This one is now ready to release to the users.
Thanks very much!!
Bill