MsgBox to display results upon closing

[greatcookies]

How can I have a message box display the results of a formula upon closing the workbook. I need to display the number of cells in a particular column less than 0. I assume you would use the countif formula (=countif(e4:e35,">0") I am going to either have display upon closing or upon hitting a command button on the sheet.

Thanks

 

[Smitty]

You're right on the money:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    MsgBox Application.WorksheetFunction.CountIf(Range("E4:E35"), "<0")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

EDIT: I forgot the close event:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    MsgBox Application.WorksheetFunction.CountIf(Range("E4:E34"), "<0")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[greatcookies]

Thanks! Works great!

 

[greatcookies]

How can I have the msgbox also say sonething like " Number of invalid counts"?

 

[Smitty]

How can I have the msgbox also say sonething like " Number of invalid counts"?

Here you go:

<font face=Courier New>    MsgBox "There are " & _
        Application.WorksheetFunction.CountIf(Range("E4:E35"), "<0") & _
        " Invalid Counts", vbOKOnly + vbInformation, "Invalid Counts"</FONT>

Where the syntax is MsgBox Primary Message, Buttons & Caption, Message Title.

Smitty

 

[greatcookies]

I greatly appreciate your help!! Works wonderful.