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

 

[russelldt]

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

I dont really understand what you mean.

I added the VBA script to the Thisworkbook section.

When I run test/run the script in the VBA module, the ’MacroName‘ window opens - it wants to run a macro. I tried to upload a screen shot, but the file 840 kb is too large. I hope this makes more sense, thanks

 

[petertenthije]

That's strange, it's not doing that for me. Do you have other macros running that might interfer?

 

[russelldt]

That's strange, it's not doing that for me. Do you have other macros running that might interfer?

Yes. There is another one, to

 

[russelldt]

Yes. There is another one, to

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge = 1 Then
If Target.Column = 11 And Target.Row > 8 And Target.Row < 63 Then
If Target.Value = "" Then
Target.NumberFormat = "General"
ElseIf Target.Value <= Date Then
Application.EnableEvents = False
Target.Value = DateAdd("yyyy", 1, Target.Value)
Application.EnableEvents = True
End If
End If
End If
End Sub

 

[petertenthije]

I don't see how that might interfer with the save macro.

Hopefully someone else can figure it out, otherwise I'll try again tomorrow. It's getting late here.

 

[russelldt]

I don't see how that might interfer with the save macro.

Hopefully someone else can figure it out, otherwise I'll try again tomorrow. It's getting late here.

??

 

[russelldt]

I don't see how that might interfer with the save macro.

Hopefully someone else can figure it out, otherwise I'll try again tomorrow. It's getting late here.

I am working on Mac OS. That is often the source of the problem.

 

[petertenthije]

I am working on Mac OS. That is often the source of the problem.

I can’t help you with that. I am part of the PC master race! LOL

 

[russelldt]

Thanks PC master?

In the meantime I will go with your first suggestion and add a Macro button on the sheet to save the file. One question, can/will this close the file after saving? That would be a great help if I could click the macro button, and it will save and close.

 

[petertenthije]

It will be with if you add the "Application.quit" code.

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