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

sneel3

Active Member
Joined
Oct 9, 2002
Messages
331
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"?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hello sneel3,
You can use something like this in the ThisWorkbook module.
Code:
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

Active Member
Joined
Oct 9, 2002
Messages
331
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

Active Member
Joined
Oct 9, 2002
Messages
331

ADVERTISEMENT

Thank you all. Sorry about the stupid questions.
 

sneel3

Active Member
Joined
Oct 9, 2002
Messages
331
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456

ADVERTISEMENT

You all are welcome. :LOL:

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.

Code:
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

Active Member
Joined
Oct 9, 2002
Messages
331
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Thanks for the kind words.
Is this possible?
Dang near anything is possible.
(Am currently working on having vba make my coffee for me!) :LOL:

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

Active Member
Joined
Oct 9, 2002
Messages
331
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.
 

Forum statistics

Threads
1,144,366
Messages
5,723,942
Members
422,527
Latest member
TotalBeginner201

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top