CODEWITHSUNDEEP
excelvbasaveconditional-statements
Khazar
Khazar

Reputation: 83

Don't save sheet if condition not met

I have a workbook with two sheets named "Daily Target" and "Staff Target".

I need code which allows saving the workbook if working on "Daily Target" Sheet, regardless of any condition.

If working on the "Staff Target" sheet allow saving if the condition meets otherwise do not save. 

Option Explicit

'I wrote that code within "Staff Target" sheet.

Sub NotSave()
    Dim WB As Workbook
    Set WB = Workbooks("Target Sales Tmplate")
    Dim ST As Worksheet
    Set ST = Worksheets("Staff Targets")        
    With ST
        If ST.Range("C" & Rows.Count).End(xlUp) <> Range("B4") Then
            WB.Close SaveChanges:=False
            MsgBox "Total Staff Target Amount is not equal to Total Monthly Target!", vbCritical, "ERROR"
        End If
    End With

End Sub

Upvotes: 0

Views: 435

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

Place this code into the ThisWorkbook module:

Option Explicit

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

    Dim staffTargets As Worksheet
    Set staffTargets = Worksheets("Staff Targets")

    With staffTargets

        If ActiveSheet.Name = .name Then

            If .Range("C" & .Rows.Count).End(xlUp) <> .Range("B4") Then
                MsgBox "Workbook Not Saved. Total Staff Target Amount is not equal to Total Monthly Target!", vbCritical, "ERROR"
                Cancel = True
            End If

        End If

    End With

End Sub

Upvotes: 1

Related Questions