need vba code to have message appear based on outcome

[3earth]

I have A11:G11 summed in cell H11. If H11 equals 0 (zero), I would like a message to pop up "You must select at least ONE location." I would like this message to pop up before close/save of the file. running excel 2007

 

[Ghris]

Not tested it but:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

[COLOR=#333333]Application.EnableEvents = False[/COLOR]
[COLOR=#333333]Set myRng = Range("H11") ' [/COLOR]
[COLOR=#333333]If Application.WorksheetFunction.Sum(myRng) = 0 Then[/COLOR]
[COLOR=#333333]MsgBox "[/COLOR][COLOR=#574123]You must select at least ONE location.[/COLOR][COLOR=#333333]"[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Set myRng = Nothing[/COLOR]
[COLOR=#333333]Application.EnableEvents = True[/COLOR]

End Sub

 

[jai9]

Hi,

Try on a copy of sheet.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("Sheet1").Range("H11") = 0 Then
    MsgBox "You must select at least ONE location."
End If
End Sub

Jai

 

[3earth]

Jai - I was not able to get this to work. Not sure what I am doing wrong. Do you have to enter the data in a certain order?

 

[3earth]

Ghris - This works but after you hit OK in the message box, the document can be closed. How do I stop the close?

 

[Ghris]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

[COLOR=#333333]Application.EnableEvents = False[/COLOR]
[COLOR=#333333]Set myRng = Range("H11") ' [/COLOR]
[COLOR=#333333]If Application.WorksheetFunction.Sum(myRng) = 0 Then[/COLOR]
[COLOR=#333333]MsgBox "[/COLOR][COLOR=#574123]You must select at least ONE location.[/COLOR][COLOR=#333333]"[/COLOR]
    If MsgBox("Click cancel to select a location", 36, "Confirm") = vbNo Then
        Cancel = True
    End If
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Set myRng = Nothing[/COLOR]
[COLOR=#333333]Application.EnableEvents = True[/COLOR]

End Sub

Not sure if this is going to work, but let me know

Edit:
Oh yes, this works I believe.

 

[3earth]

The confirm shows up with Yes and No options. No = back to sheet (to hopefully have someone select a location). Yes = document closes. Almost. Is there anything else that can be done?

 

[3earth]

Thank you Ghris. I will live with this. I am really greatful for your help.