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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

jsiason

Board Regular
Joined
Nov 9, 2005
Messages
72
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.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459

ADVERTISEMENT

1. Press Alt-F11 to bring up VBA
2. In the left pane, double-click "ThisWorkbook"
3. Paste the code into the right pane.
 

jsiason

Board Regular
Joined
Nov 9, 2005
Messages
72
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!
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459

ADVERTISEMENT

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
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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
 

jsiason

Board Regular
Joined
Nov 9, 2005
Messages
72
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.
 

jsiason

Board Regular
Joined
Nov 9, 2005
Messages
72
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

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