Solved: Only save with a Macro Button

[burley]

Hi,
I'm a novice, and I have a spreadsheet that I want users to save only via a button on the worksheet. I have found code that prevents saving, which is great:-

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

MsgBox "This workbook can ONLY be saved with the Save Button"
Cancel = True

End Sub

but then my save as Macro wont work either...

Sub Saveasdate()

Dim strName As String
Dim strPath As String
Application.DisplayAlerts = False

strName = Range("AL3").Value
strPath = ActiveWorkbook.Path
ActiveWorkbook.SaveAs Filename:=strPath & "\" & strName, FileFormat:=xlNormal
End Sub


Can someone help me to do this better, or somehow turn saving back on when I need it?

Thank you

[Bob Phillips]

Change the Workbook Open code to



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If ButtonSave Then

ButtonSave = False

Else
MsgBox "This workbook can ONLY be saved with the Save Button"
Cancel = True
End If
End Sub


Declare a Public variable in the standard module of



Public ButtonSave As Boolean


then your macro becomes



Sub Saveasdate()

Dim strName As String
Dim strPath As String
Application.DisplayAlerts = False

strName = Range("AL3").Value
strPath = ActiveWorkbook.Path
ButtonSave = True
ActiveWorkbook.SaveAs Filename:=strPath & "\" & strName, FileFormat:=xlNormal
End Sub

[burley]

You are a Legend

Many Many thanks