Disallow Save unless in specific Macro (Excel)

[RP1America]

I have a template workbook that must be saved by initiating an on-sheet command button. I have code below that disallows the user to save via the normal channels, yet still allows me (my user id) to do so to make updates to the original document.

However, I DO need the user to be able to save via coding within the command button's macro.

Thoughts on how to accomplish this?

ThisWorkbook

Public Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    If Environ("username") = "n264rsp" Then
    bSave = True
    Else
    bSave = False
    End If
    
    If Not bSave Then
    Cancel = True
    MsgBox "You must use the Save & Exit command button on the Dashboard Sheet"
    End If
    
End Sub

Command Button Macro

Option Explicit

Public bSave As Boolean


Public Sub SaveExit()

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "K:\GA2PT - Group Annuity to Trust Conversions\Current Conversion Checklists\" _
    & strPlan & " - GA2PT Conversion.xlsm", FileFormat:=52
    Application.DisplayAlerts = True
    

End Sub

 

[SkipVought]

hi,

You could assign bSave in your SaveExit procedure and then in the event, code around bSave to exclude the environ stuff.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RP1America]

Something like this?

ThisWorkbook

Public Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
If bSave = False Then
    If Environ("username") = "n264rsp" Then
    bSave = True
    Else
    bSave = False
    End If
    
    If Not bSave Then
    Cancel = True
    MsgBox "You must use the Save & Exit command button on the Dashboard Sheet"
    End If
End If

End Sub

Command Button Macro

Option Explicit

Public bSave As Boolean


Public Sub SaveExit()

    Application.DisplayAlerts = False
    bSave = True
    ActiveWorkbook.SaveAs "K:\GA2PT - Group Annuity to Trust Conversions\Current Conversion Checklists\" _
    & strPlan & " - GA2PT Conversion.xlsm", FileFormat:=52
    Application.DisplayAlerts = True
    

End Sub

 

[PHV]

Public Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Environ("username") <> "n264rsp" And Not bSave Then
    Cancel = True
    MsgBox "You must use the Save & Exit command button on the Dashboard Sheet"
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886