Setting Up Required Data Values

jsiason

Board Regular
Joined
Nov 9, 2005
Messages
72
Hi! I will very much appreciate anyone's help on this.

I am trying to set the Excel spreadsheet to require values in a specific cell such that some form of pop-up warning/comment will appear if this cell is left blank when saving the file.

Do you know how? I find this very tricky...

Thank you so much!


Edited by Nate: Less caps and less bolding.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Welcome.

Check out "validation" from the "data" menu. Check the Excel help files on this topic and post back with any issues.

Probably not as hard as you think.
 
Upvote 0
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'ThisWorkbook code module code!

Sheet_Name = "Sheet1"

If Sheets(Sheet_Name).Range("A4").Value = xlNull Then MsgBox "You must have a value in Cell: A4!" & _
vbLf & vbLf & "Canceling your save!" & _
vbLf & vbLf & "Please supply a value for Cell: A4.": Cancel = True: Exit Sub

If Sheets(Sheet_Name).Range("A4").Value > 1000 Then MsgBox "The value in A4 exceeds the 1,000 limit!"

If Sheets(Sheet_Name).Range("A4").Value < 100 Then MsgBox "The value in A4 is less than 100 limit!"

If Sheets(Sheet_Name).Range("A1").Value = xlNull Then MsgBox "The value in A1 is missing!"

If Sheets(Sheet_Name).Range("A1").Value < 1000 Then MsgBox "The value in A1 is less than 1,000 limit!"
End Sub
 
Upvote 0
OMG, i forgot to say i am very new to this... :eek:

JoeWas, where do I paste your code? Im sorry if I sound dumb...

Thank you so much.
 
Upvote 0
1. Press Alt-F11 to bring up VBA
2. In the left pane, double-click "ThisWorkbook"
3. Paste the code into the right pane.
 
Upvote 0
Thanks for that! :biggrin:

How about if I just need a notification/message that value was not entered, but spreadsheet can still be saved?

Thanks for the big help! I am in debt...

-----------------
This site is great! It goes to my favorites!
 
Upvote 0
Sure, just remove:
.": Cancel = True: Exit Sub

So something like:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheet_Name = "Sheet1"
If Sheets(Sheet_Name).Range("A4").Value = xlNull Then MsgBox "You must have a value in Cell: A4!"
If Sheets(Sheet_Name).Range("A4").Value > 1000 Then MsgBox "The value in A4 exceeds the 1,000 limit!"
If Sheets(Sheet_Name).Range("A4").Value < 100 Then MsgBox "The value in A4 is less than 100 limit!"
If Sheets(Sheet_Name).Range("A1").Value = xlNull Then MsgBox "The value in A1 is missing!"
If Sheets(Sheet_Name).Range("A1").Value < 1000 Then MsgBox "The value in A1 is less than 1,000 limit!"
End Sub
 
Upvote 0
If you need to do this on a range, something like:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range
Sheets("Sheet1").Range("A4:A15").Select
For Each cell In Selection
If cell.Value = 0 Then MsgBox ("Cell A" & cell.Row & " does not contain a value")
Next cell
Sheets("Sheet1").Range("A1").Select
End Sub
 
Upvote 0
I have this but i get a Runtime error 9 (subscript out of range).

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range
Sheets("CCA Cyle").Range("G7:G15").Select
For Each cell In Selection
If cell.Value = 0 Then MsgBox ("Cell G" & cell.Row & " does not contain a value")
Next cell
Sheets("CCA Cycle").Range("G1").Select
End Sub

What seems to be the problem?
Thanks.
 
Upvote 0
It seems that the code wasnt running how I expected it would. Can I explain a little, hoping you will help me?

In 1 file, I have 5 worksheets, and I want the code to run only in Sheet 3, which has been renamed CCA Cycle.

I need a message box before save if there is no value in G7 to G100, but I was hoping message box should appear only once in a collective manner(the code you gave prompted the message box for the same no. of times that there was no value found in the specified range).

And after message box will appear, workbook can still be saved even if there's no value. I just need the user to be notified.

Experts, please help! :cry:
Thanks!!
 
Upvote 0

Forum statistics

Threads
1,212,143
Messages
6,106,213
Members
448,006
Latest member
oreo2996

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
Back
Top