Stopping the save function...until a user has clicked "save as".

[ExcelToyney]

Hello folks,

Your favourite nooby here.

Basically I want to block the 'save' function of an excel workbook. Being a template I want the user to have to Save As, to keep the original template in tact, then they can do whatever they want with it once it's on their local system.

Stopping the save function and displaying a nice message is part 1 of the issue.

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

If SaveAsUI = False Then

    Cancel = True

    MsgBox "You cannot save over this Template.  Use 'Save As' and Rename"

End If

End Sub

This works perfectly for that....however, once they have renamed, they STILL need to "Save As" after, which is annoying.

Is there something I can add to this code to stop this once the user has changed the name of the workbook in someway?

I was thinking something along the lines of:

(This is in Toyneycode, the language of my brain and in no way affiliated with Excel!)

If Workbook is called "Template" then run this code (as shown above)

If False, then don't?!

In my brain, as soon as they "saved as" and therefore changed the name of the workbook, the code would realize this and allow them to 'save' as normal again!

Or am I just being too hopeful!

Thanks in advance

Toyney

[Marcol]

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
    If ActiveWorkbook.Name = "Template.xls" Then
        Cancel = True
        MsgBox "You cannot save over this Template."
    End If
End If
End Sub

Or a variation to suit

[dominicb]

Good morning ExcelToyney

If you have no other event driven code, then just use this line after the user has used Save As :

Application.EnableEvents = False

This will simply disable the event that fires when the user attempts to save - so the user doesn't get the warning.

HTH

DominicB

[ExcelToyney]

I've just realised that doesn't work perfectly.

For example, if somebody tries to save, it will bring up that message and they will probably save as. However, if it brings up the message and they then quit the spreadsheet, it then prompts them to save the sheet. As the code has stopped working.

Basically I need the sheet to not allow them to just 'save' until they have saved as something different, when they have, then they can save again.

[Marcol]

Try this ------- (or a variation to suit your needs)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = False Then
        If ActiveWorkbook.Name = "Template.xls" Then
            Cancel = True
            MsgBox "You cannot save over this Template...Use SaveAs And Rename"
            SaveAsUI = True
            Application.Dialogs(xlDialogSaveAs).Show
        End If
    End If
End Sub

It's not bullet proof - the user could overwrite in the saveas dialog box response

"The file Template.xls already exists. Do you want to replace the existing file?"

Or save in a different location

Just how hard do you want try to stop wrong input?