VBA BeforeCLose

[excelgal2016]

Hi all,

I have the below code and this is what I want to achieve:

IF L1=1 AND B1="" AND C1=""

then I want to prevent the file from saving. In all the other scenarios, the file should be saved. I need to also be able to specific Sheet name.

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


If (Sheets("Sheet1").[L1] = "1" And Sheets("Sheet1").[B1] = "" And Sheets("Sheet1").[C1] = "") Then


Cancel = True
MsgBox "Save disabled. Custom message comes here"
Else
Cancel = False
End If
End Sub

This code works but it disregards cell C1. I also want to be able to add more cells that can't be blank if required.

Anyone who can help?

Thank you

 

[excelgal2016]

Respectively, if anyone knows a better solution, this is what I'm looking to achieve:

If cell A1 is empty then we're all good. Document saves normally.

BUT

If cell A1 has a value, then cells B1, C1, ... need to be completed as well, otherwise document won't save.

 

[stumac]

Are you sure you want an AND for the second one and not an OR.

With AND then both B1 & C1 need to be empty with OR it will stop if either are. the code you posted looks like it will work and even works with a quick test.

To make it easier for adding more conditions consider using a with:

With Sheets("Sheet1")
    If (.[L1] = "1" And (.[B1] = "" OR .[c1] = "")) Then
        Cancel = True
        MsgBox "Save disabled. Custom message comes here"
    End If
End With

You shouldn't need the else statement as the Boolean is false by default.

 

[excelgal2016]

Are you sure you want an AND for the second one and not an OR.

With AND then both B1 & C1 need to be empty with OR it will stop if either are. the code you posted looks like it will work and even works with a quick test.

To make it easier for adding more conditions consider using a with:

With Sheets("Sheet1")
    If (.[L1] = "1" And (.[B1] = "" OR .[c1] = "")) Then
        Cancel = True
        MsgBox "Save disabled. Custom message comes here"
    End If
End With

You shouldn't need the else statement as the Boolean is false by default.

It works with OR. Thank you!