Message box when document saved - if value in cell empty

[smakatura]

I record customer requests on a daily basis. Some of these requests require actions by other teams so I must fill out other forms and send to other teams when these types of requests arise. there are requests that are not important to other teams but are written on the same initial sheet.

Sometimes an important one that must go to another team has slipped through the cracks and not been reported. I am looking for some way to keep this from occurring while working within my current system retraints. I am envisioning a message box that pops up when a save a document....

I need a message box to pop up when I save an excel document to ask me if I recorded and reported one of the important request types. I have a "Recorded?" column so that once I have recorded and reported the item I can check it off and it will not show up as my pop up reminder.

Scenario
I have types "New, Delete and Change" that will show up on the popup until I mark that the item has been recorded. as you can tell there is one Delete item below that is already marked as recorded that is not in the pop up message below.

There are unimportant types that will always be marked as "recorded", so these will not show up on the popup.

so lets say I have the data

Number<o></o>	Region<o></o>	Request type<o></o>	Recorded?<o></o>
1<o></o>​	A<o></o>	New<o></o>	<o></o>
5<o></o>​	A<o></o>	Delete<o></o>	X<o></o>
7<o></o>​	A<o></o>	Change<o></o>	<o></o>
8<o></o>​	B<o></o>	Unimportant 1<o></o>	X<o></o>
4<o></o>​	A<o></o>	Unimportant 2<o></o>	X<o></o>
6<o></o>​	B<o></o>	Unimportant 3<o></o>	X<o></o>
7<o></o>​	B<o></o>	Delete<o></o>	<o></o>
4<o></o>​	B<o></o>	Delete<o></o>	<o></o>
3<o></o>​	B<o></o>	Unimportant 2<o></o>	X<o></o>
7<o></o>​	B<o></o>	Unimportant 3<o></o>	X<o></o>
9<o></o>​	A<o></o>	Change<o></o>	<o></o>

<tbody> </tbody>

I want the message box to ask me about the important items that are not marked as recorded. So when I save the document I want the message box to say

Did you remember to Record and Report?

1 - A - New

7 - A - Change

7 - B - Delete

4 - B - Delete

9 - A - Change

<colgroup><col width="64" style="width: 48pt;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="64" style="width: 48pt;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <tbody> </tbody>

 

[Perpa]

smakatura,
Assuming your data is in columns A,B,C, and D and your header is in row 1...
The macro below will produce the message box if any of the values in column D are blank. It could be run using a command button to SAVE your file. If there are blank cells in column D then the file would NOT be saved. If you still want to save, you could do so using the normal save methods.

To test this macro copy and save it to a standard module using Alt + F11 and paste the code in the window that opens. Save the file as macro enabled. To test run press Alt+F8, then select the name 'Msg_IF_Blank', and press 'Run'.
Perpa

Sub Msg_IF_Blank()
Dim lr, rw As Long
lr = Range("C" & Rows.Count).End(xlUp).Row   'Use column C which always has data
    For rw = 2 To lr
         If Cells(rw, "D") = "" Then
              myMsg = myMsg & Chr(10) & Cells(rw, "A") & "-" & Cells(rw, "B") & "-" & Cells(rw, "C")
         End If
    Next rw
If myMsg = "" Then
     ActiveWorkbook.Save
Else
     Message = MsgBox("Did you remember to Record and Report these items..." & Chr(10) & myMsg)
End If
End Sub

 

[Fluff]

Another option

Sub Check()
   Dim Rng As Range
   Dim Rn As Range
   Dim Msg As String

   On Error GoTo NoBlanks
   For Each Rng In Columns(4).SpecialCells(xlBlanks).Areas
   On Error GoTo 0
      If Rng.Count = 1 Then
         Msg = Msg & vbLf & Join(Application.Transpose(Application.Transpose(Rng.Offset(, -4).Resize(, 3).Value)), " - ")
      Else
         For Each Rn In Rng
            Msg = Msg & vbLf & Join(Application.Transpose(Application.Transpose(Rn.Offset(, -3).Resize(, 3).Value)), " - ")
         Next Rn
      End If
   Next Rng
   Msg = "Did you remember to Record and Report?" & vbLf & Msg
   MsgBox Msg
Exit Sub
   
NoBlanks:
   MsgBox "All OK"

End Sub

 

[smakatura]

thanks