Disable Save unless via code 2

[RP1America]

I have a command button on an Excel sheet that runs some code and then saves the workbook. I need for any other save options to be disabled.

In my code below, I think my issue may have to do with bSave and me not understanding how the boolean value works. Any advice?

I have this in a module, run by my on sheet command button:

Public Sub SaveExit()

    Dim bSave As Boolean
    bSave = True

    'code...

End Sub

And this in ThisWorkbook:

Option Explicit

Public WithEvents App As Excel.Application


Public Sub Workbook_Open()

Set App = Application
    
    Application.CommandBars("File").Controls("Save As...").Enabled = False
    Application.CommandBars("File").Controls("Save").Enabled = False
    
    Options.SaveInterval = 0

End Sub

Public Sub Workbook_Close()
    
    Set App = Nothing
    ActiveWorkbook.Saved = True
    Options.SaveInterval = 10
    
End Sub

Public Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Dim bSave As Boolean
    If bSave = True Then
    Exit Sub
    Else
    Cancel = True
    MsgBox "You must use the Save & Exit command button on the Dashboard Worksheet"
    End If
    
End Sub

 

[SkipVought]

hi,

If you assign TRUE to Cancel, the save will be cancelled.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

What I was hoping to accomplish was by setting bSave = True when the SaveExit procedure is run, that this would allow save (hence the IF statement in Workbook_BeforeSave) and disallow save if the user is attempting to save any other way.

I am thinking I am not using this correctly, but unfortunately don't know any better.

If I had not shown my (bad) code and instead just said...I want to disable the File>Save/Ctrl+S/Ribbon_icon save options and only save through VBA, what suggestions could you throw my way?

 

[SkipVought]

I have a Named Range cell named CodeCancel.

Enter TRUE in the CodeCancel cell in the WorkbookOpen event.

Whenever YOU want to save the workbook, put FALSE in CodeCancel and then SAVE the workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = [CodeCancel]
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

RP1America, in your posted code you should declare bSave only once at the module level (global variable).

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

 

[RP1America]

Thank you to both!

I declared bSave globally and all is working well, EXCEPT...how do I save the workbook now that it has this code in it!? Haha!

 

[PHV]

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

    If  Not bSave Then
      Cancel = True
      MsgBox "You must use the Save & Exit command button on the Dashboard Worksheet"
    End If
    
End Sub

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

 

[RP1America]

Hmm...it "works", but I still can't save the Workbook with this code in it.

I seem to have created a bit of a paradox. I've added code to the Workbook so that it cannot be saved. Yet, I need to save it with this code in it. What am I missing?

 

[PHV]

What is the actual code of SaveExit ?

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

 

[RP1America]

Option Explicit

Public bSave As Boolean


Public Sub SaveExit()

    Application.ScreenUpdating = False

    bSave = True

    Dim strPlan As String, strTotal As String, strTS As String, strTMT As String, strPSC As String, _
    strSM As String, strRG As String, strDU As String, strTMDC As String

    strPlan = Range("C5").Value
    strTotal = Range("F5").Value
    strTS = Range("F7").Value
    strTMT = Range("F14").Value
    strPSC = Range("F24").Value
    strSM = Range("F31").Value
    strRG = Range("F33").Value
    strDU = Range("F37").Value
    strTMDC = Range("F46").Value

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "I:\GA2PT Project\Conversion Checklists\Current Conversion Checklists\" _
    & strPlan & " - GA2PT Conversion.xlsm", FileFormat:=52
    Application.DisplayAlerts = True

    Workbooks.Open ("I:\GA2PT Project\Conversion Checklists\GA2PT Conversion Dashboard.xlsx")
    
    Sheets("Dashboard").Unprotect Password:="GA2PT"
    Sheets("Template").Unprotect Password:="GA2PT"
        
    Dim FoundRange As Range
    Worksheets("Dashboard").Activate
    Set FoundRange = Sheets("Dashboard").Cells.Find(what:=strPlan, LookIn:=xlFormulas, lookat:=xlWhole)
    If FoundRange Is Nothing Then
    
    Sheets("Template").Visible = True
    Sheets("Template").Select
    Rows("3:11").Select
    Selection.Copy
    Sheets("Template").Visible = False
    Sheets("Dashboard").Select
    
    Dim LastRowD As Object
    Set LastRowD = Sheets("Dashboard").Range("D65536").End(xlUp)

    LastRowD.Offset(2, -3).Select
    ActiveSheet.Paste

    Dim LastRowB As Object
    Set LastRowB = Sheets("Dashboard").Range("B65536").End(xlUp)
    Dim MyPath As String, MyFile As String
    MyPath = "I:\GA2PT Project\Conversion Checklists\Current Conversion Checklists\"
    MyFile = strPlan & " - GA2PT Conversion.xlsm"
    LastRowB.Offset(1, 0).Formula = "=HYPERLINK(""" & MyPath & MyFile & """,""" & strPlan & """)"
    LastRowB.Offset(1, 0).Font.Bold = True
    LastRowB.Offset(1, 0).Font.ColorIndex = 3
    LastRowB.Offset(1, 1).Value = strTotal
    LastRowB.Offset(1, 1).NumberFormat = "#%"
    LastRowB.Offset(2, 3).Value = strTS
    LastRowB.Offset(3, 3).Value = strTMT
    LastRowB.Offset(4, 3).Value = strPSC
    LastRowB.Offset(5, 3).Value = strSM
    LastRowB.Offset(6, 3).Value = strRG
    LastRowB.Offset(7, 3).Value = strDU
    LastRowB.Offset(8, 3).Value = strTMDC
    
    Else
    FoundRange.Offset(0, 1).Value = strTotal
    FoundRange.Offset(0, 1).NumberFormat = "#%"
    FoundRange.Offset(1, 3).Value = strTS
    FoundRange.Offset(2, 3).Value = strTMT
    FoundRange.Offset(3, 3).Value = strPSC
    FoundRange.Offset(4, 3).Value = strSM
    FoundRange.Offset(5, 3).Value = strRG
    FoundRange.Offset(6, 3).Value = strDU
    FoundRange.Offset(7, 3).Value = strTMDC
    
    End If
    
    Range("A1").Select
    
    Sheets("Dashboard").Protect Password:="GA2PT"
    Sheets("Template").Protect Password:="GA2PT"
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "I:\GA2PT Project\Conversion Checklists\GA2PT Conversion Dashboard.xlsx", FileFormat:=51
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True
    
    ActiveWorkbook.Close

End Sub

 

[SkipVought]

Do you have this code in only ONE WORKBOOK???

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

    If  Not bSave Then
      Cancel = True
      MsgBox "You must use the Save & Exit command button on the Dashboard Worksheet"
    End If
    
End Sub

I see you are opening other workbooks. Do you expect the other workbooks to ALSO be subject to this logic?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Yes, only in one workbook. No, I do not expect the other workbook to be subject to this logic, just the one that contains the code.

But now that I've added this code, I can't save it. Which is the result I need...but only for the user. Perhaps I need to add a new procedure that sets bSave to True and just simply saves this workbook.

 

[SkipVought]

In the code that assigns the value of vSave, put some logic the excludes you like..

if environ("username") = "???YourId???" then
  bSave = true
else
  bSave = false
end if

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}