Save excel file based on a cell condition

[russelldt]

I want to be able to save an excel file, only when specific data have been entered

I can create an if(and( formula to validate the input that is required, and place the result “1” in cell A1. If cell A1 is 1, the file can be saved, (not save as). If cell A1 is ‘0’, a message should appear to complete data in the cells.

Thanks

 

[petertenthije]

Sub Macro1()

If Sheets("Sheet1").Range("A1") = 1 Then
    ActiveWorkbook.Save
Else
    MsgBox "You are missing data"
End If

End Sub

 

[russelldt]

Sub Macro1()

If Sheets("Sheet1").Range("A1") = 1 Then
    ActiveWorkbook.Save
Else
    MsgBox "You are missing data"
End If

End Sub

Thanks for the prompt reply


This works (ie I get the error message when the condition is not met) when I test run it in the VBA developer mode, but as soon as I return to the actual sheet (i replaced Sheet1 with Cashflow input) I am able to save it without the condition being met.

 

[russelldt]

Thanks for the prompt reply


This works (ie I get the error message when the condition is not met) when I test run it in the VBA developer mode, but as soon as I return to the actual sheet (i replaced Sheet1 with Cashflow input) I am able to save it without the condition being met.

I have assumed that I would be able to Save the file as normal, i.e not using a macro button.

 

[petertenthije]

The "1" you entered in cell A1, has that been entered as a numerical entry or a text entry?

 

[russelldt]

The "1" you entered in cell A1, has that been entered as a numerical entry or a text entry?

 

[russelldt]

It’s numerical, but I can change that to a True, depending on how I set up my If(and( criteria. To test it now, I just put a 1 on cell A1

 

[petertenthije]

Save this not in a seperate module, but in the ThisWorkbook section of VBA.

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

If Sheets("Sheet1").Range("A1") = 1 Then
    ActiveWorkbook.Save
Else
    MsgBox "You are missing data"
    Cancel = True
End If

End Sub

 

[russelldt]

Save this not in a seperate module, but in the ThisWorkbook section of VBA.

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

If Sheets("Sheet1").Range("A1") = 1 Then
    ActiveWorkbook.Save
Else
    MsgBox "You are missing data"
    Cancel = True
End If

End Sub

I added the VBA to the ThisWorkbook and when i run it, the Macro Name window pops up

 

[petertenthije]

I dont really understand what you mean.