-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
VBA code for Pop-Up when cells are blank or zero
- Thread starter sneel3
- Start date
HalfAce
MrExcel MVP
- Joined
- Apr 6, 2003
- Messages
- 9,456
Hello sneel3,
You can use something like this in the ThisWorkbook module.
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
Upvote
0
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?
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?
Upvote
0
HalfAce
MrExcel MVP
- Joined
- Apr 6, 2003
- Messages
- 9,456
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?
Upvote
0
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.
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.
Upvote
0
HalfAce
MrExcel MVP
- Joined
- Apr 6, 2003
- Messages
- 9,456
You all are welcome. 
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.
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
Upvote
0
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?
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?
Upvote
0
HalfAce
MrExcel MVP
- Joined
- Apr 6, 2003
- Messages
- 9,456
Thanks for the kind words.
(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?
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?
Upvote
0
Similar threads
- Solved
