I Can't Save Because I said Not to-CodePudding

I have a code that stops people from saving a template file if they don't have a certain field filled in.

The idea is, they open the template file (which opens read-only), fill it out, save-as, then submit to me to fill in what I need to fill in.

The code words great... here it is:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("Sheet1").Range("E1").Value = "" Then
    Cancel = True
    Response = MsgBox("Please enter the data in E1", vbCritical, "Error!")
End If
End Sub

The problem is... now I can't save the template, because that cell is blank! And I can't fill it in and then save the template because that would just defeat the purpose of the code.

Feel like I've painted myself into a corner here... any ideas how to save this template so that field is blank?

Is there a way to key off the "Save-AS" function instead of the "Save" function? I'll always be able to Save the Template, but since it's Read-Only for everyone else, they will always have to Save-AS...

CodePudding user response：

Add a condition, as Scott Craner mentioned. The only risk might be if someone by chance had the same username as you and saved with the field still blank!

If Application.UserName = "[your UserName]" then 
    'save
ElseIf Sheets("Sheet1").Range("E1").Value = "" Then
    'cancel save
End if