Macro to stop me closing a workbook if F1 is empty?

[tonywatsonhelp]

Hi Everyone,

I need a macro that when I go to close the workbook down it looks at sheet "welcome" cell F1 and if it says "Submitted" then closes but if not brings up a message box saying
"Data not sent"
"Do You wish to close document?"
"Any Changes will be lost"

Yes No,

if they click yes it closes
if they click no it stays open.

Please help if you can

Thanks

Tony

 

[Roderick_E]

How about:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
If Sheets("Welcome").Range("F1") <> "Submitted" Then
promptit = MsgBox("Changes will be lost. Do you want to close document?", vbYesNo, "DATA NOT SENT")
If promptit = vbNo Then
    Cancel = True
    Exit Sub
End If
End If
Resume Next
End Sub

 

[Scott T]

This code should go into the thisworkbook module. If the user clicks yes then the file closes and does not popup to save changes. There is nothing to stop the user from saving before closing. If F1=submitted then the file closes as normal and will save the file before closing.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not UCase(Sheets("welcome").Range("F1")) = "SUBMITTED" Then
    ans = MsgBox("Data not sent" & Chr(10) & "Do You wish to close document?" & Chr(10) & "Any Changes will be lost", vbYesNo)
    ThisWorkbook.Saved = True
Else
ThisWorkbook.Save
End If
If ans = vbNo Then Cancel = True
End Sub

 

[tonywatsonhelp]

Thank you Roderick and Scott, both a great help, both do a great job and exactly what I wanted.

Thank you for your help

Tony