VBA code for Pop-Up when cells are blank or zero

[sneel3]

I need to warn users that there are blank cells that need to be filled in. I would like to do this when they try to save (or save as) the file. How do I create a pop-up that tells them that "There are empty cells that need to be filled in"?

 

[HalfAce]

Hello sneel3,
You can use something like this in the ThisWorkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim ChkRng As Range, c As Range

With Sheets("Sheet1") 'Change to the sheet of choice
  Set ChkRng = .Range("A1:C10") 'Change to the range of choice
  For Each c In ChkRng
    If IsEmpty(c) Then
      Cancel = True
      Sheets("Sheet1").Select
      c.Select
      MsgBox ("All cells in the range " & ChkRng.Address(0, 0) & " must have an entry before saving."), , "Save cancelled"
      Exit For
    End If
  Next c
End With
End Sub

 

[sneel3]

Okay,

I copied what you sent me (from "Private" to "End Sub")
I opened the desired workbook in Excel
I pressed Alt+F11
I clicked on Insert / Module
I pasted what you sent me into the new module
I changed "Sheet1" to "Form" (which is the name of the tab in my spreadsheet)
I changed "A1:C10" to "M11:N11" as a range to test (this range is currently empty)
I clicked save (in VBA)
I went back to Excel
I clicked Save
Nothing happened

What else do I need to do?
Why can't I see this on my list of macros when I click Run Macro?

 

[HalfAce]

Why can't I see this on my list of macros when I click Run Macro?

You won't see it in your list of macros (when you press Alt+F8) because
it's a private sub.
To correct that just go into the module where it resides and replace 'Private' with 'Public' - or
you can backspace out 'Private' so it begins with just 'Sub'.

Next, (when you hit Alt+F8), you should see 'CheckYearDemo'.
Make sure you have the range M11:N11 (with dates in them) selected when you select the
macro name and hit Run.

That work?

 

[sneel3]

Thank you all. Sorry about the stupid questions.

 

[sneel3]

One more thing:

To make the range I am referencing the size I need it to be, I need to see how many rows are populated and have the range equal to M11:N??. So, if cells in column L are populated down to row 1000, the range would be M11:N1000 and P11:P1000.

This seems easy, but it is not working for me.

 

[HalfAce]

You all are welcome.

Sorry about the stupid questions.

The only stupid questions are the ones that don't get asked. :wink:

To make it work in columns M:N (and column P also, right?) down to the last used row
in column L, you can do this.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim LstRw As Long, i As Long, c As Range
With Sheets("Sheet1") 'Change to the sheet of choice
  LstRw = .Cells(Rows.Count, "L").End(xlUp).Row
  For i = 11 To LstRw
    If IsEmpty(.Cells(i, "M")) Or _
      IsEmpty(.Cells(i, "N")) Or _
      IsEmpty(.Cells(i, "P")) Then
        Cancel = True
        .Select
        For Each c In .Range(.Cells(i, "M"), .Cells(i, "P"))
          If IsEmpty(c) And Not c.Column = 15 Then
            c.Select
            Exit For
          End If
        Next
        MsgBox ("All cells in the ranges" & Chr(10) & _
                "M11 : N" & LstRw & " and" & Chr(10) & _
                "P11 : P" & LstRw & Chr(10) & _
                "must have an entry before saving."), , "Save cancelled"
        Exit For
    End If
  Next
End With

End Sub

 

[sneel3]

You are a genius. I have been informed that a decision has been made that cell L4 must = 0 and I am to allow the spreadsheet to be saved after the pop-up has been viewed. In other words, the pop-up would read:

All cells in ranges M11:N?? and P11:P?? should have an entry and cell L4 must equal zero.

Click OK to continue saving the document or click Cancel to edit the document further.

Is this possible?

 

[HalfAce]

Thanks for the kind words.

Is this possible?

Dang near anything is possible.
(Am currently working on having vba make my coffee for me!)

OK, so the save 'anyway' becomes an option. No problem.
How about L4 equaling 0? Is this something (perhaps the return from a formula?) for which,
if it doesn't already equal 0 requires the user to change some other value(s) until it does,
or can we simply insert a zero in with the code?
If it requires input or editing from the user, then is there a range we can bring the user
to before quitting the code?

[EDIT:]
I forgot to ask, is L4 required to equal 0? - (as in cancel the save if it doesn't?) - or should
the user still be allowed to save if it doesn't?

 

[sneel3]

L4 is the result of a formula. It should = 0; however, we will let them save anyway after they see the warning. No input will be needed by the code or the user.