2 Commands in same Macro in "ThisWorkBook"?

  • Hi,

    I have VBA code that locks my workbook when closed if someone unlocks a sheet:

    I found code that will stop someone from clicking save button but the only way I know to get it to work is to place it in ThisWorkBook module by itself. I'm trying to get both codes to work. Can I put them in together somehow? I tested doing that to no avail. Here's the other bit of code

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        If Not SaveAsUI Then
            Cancel = True
            MsgBox "You cannot save this workbook. Use Save As if this is not a blank", vbInformation, "MJM"
        End If
    End Sub

    FYI, I've created workbooks for employee"s. They all have "blank" copies. When they fill them out I have buttons for them to save their work that re-names the blank rather than overwriting it, however, its second nature to just click the "save" button. If they do, they've lost their "blank" so I love this idea which will make them pause and not do it. Occasionally, they also unlock their workbook blanks and the other command ensures its re-locked when they close it so they dont accidentally screw up any formulas next time they open it.

    Thanks for any help!

  • If you save the "blank" as a template file (.XLTX) or a macro enabled template file (.XLTM) then hitting the Save option (or even the Ctrl+S key combo) will force the user to have to "Save As" by default.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!