Triggered Message Box to run code

Watchdawg

Board Regular
Joined
Jan 21, 2015
Messages
62
Sorry if this has been addressed, I've seen things that come close but don't hit the mark.
I have a spreadsheet, column J indicates a Yes or No via formula to determine if a pump requires charging. What I need is prior to the worksheet closing, a message box should appear (if any cells in column J indicate "Yes") asking if you would like to send the email. I have code written to copy the appropriate information into an outlook email and the user hits the "Send" button on outlook.
How do I get this to happen prior to closing the workbook.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,851
Office Version
  1. 2013
Platform
  1. Windows
First you said:
What I need is prior to the
worksheet
closing

Then you said:
How do I get this to happen prior to closing the
workbook
.

So do you mean Worksheet or Workbook?

And worksheets do not open or close.
They are active or not active

 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,480
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

   With Worksheets("[COLOR=#ff0000]Sheet1[/COLOR]")
      If WorksheetFunction.CountIf(.Range("J:J"), "yes") > 0 Then
         If MsgBox("Do you want to send email?", vbYesNo) = vbYes Then Call [COLOR=#ff0000]emailMacro[/COLOR]
      End If
   End With
End Sub
Change values in red to suit
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,685
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Seems like you need a workbook event, copy following into your workbook object, adjust the code as fits your sheet:
Rich (BB code):
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Dim x           As Long
    Dim str(1 To 2) As String
    str(1) = "Pump(s) require changing, send mail?"
    str(2) = "Send Mail for Changing Pumps?"
    
    With Sheets("Sheet1")
        x = .Cells(.Rows.Count, 10).End(xlUp).Row
        If Application.CountIf(.Cells(1, 10).Resize(10), "Yes") And MsgBox(str(1), vbYesNoCancel, str(2)) = vbYes Then
            'code to send email
        End If
        Erase str
    End With

End Sub
 
Last edited:

Watchdawg

Board Regular
Joined
Jan 21, 2015
Messages
62
OK, I think I've been playing with this too long, I am now getting a lot of bugs in the email portion as well. I think I have muddied the waters sufficiently enough that I can't see daylight. Going to start clean on this one, you guys always offer up ideas that are clean and I'm able to learn from them...

Column I (1st Recharge Date) uses a formula to determine X number of weeks from the valid date in column G. If that date is past due for charge, column J indicates "Yes", if not then "No".
If any line in column J indicates "Yes", I need a message box to pop up indicating that there are pumps that require charging. And do you wish to send the update now?
If "Yes", then the information in Column C in line with the Column J "Yes" is copied and pasted into an outlook email body for distribution.
If there are no pumps requiring charging, the pop up does not appear.
Unfortunately, it doesn't appear that I can attach a sample spreadsheet for visibility, so I'll cut/paste

Thanks and thanks for your patience!
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,685
Office Version
  1. 365
Platform
  1. Windows
What is your code to generate the email? I'm guessing you haven't got it linked to the loop that's testing for Yes/No so that each email generated is specific to that row.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,277
Messages
5,527,736
Members
409,786
Latest member
AbdulMoix

This Week's Hot Topics

Top