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.
 
Try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range, Msg, Style, Title, Response, CellRef
Sheets("CCA Cyle").Range("G7:G15").Select
For Each cell In Selection
If cell.Text = "" Then CellRef = CellRef & vbLf & "G" & cell.Row
Next cell
Msg = "The following cells do not contain values:" & vbLf & CellRef & vbLf & "Do you want to continue?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbNo Then
Cancel = True
Sheets("CCA Cyle").Range("G1").Select
Exit Sub
End If
Sheets("CCA Cyle").Range("G1").Select
End Sub
 
Upvote 0

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,)
You know, it did work (yeeey!), but only when my current sheet is CCA Cycle. When I go to Sheet1 or any other sheets (and not CCA Cycle sheet) and I try to save, an error appears:

Runtime error '1004'
Select method of Range class failed.

If I debug, this one's highlighted (third line in the script):

Sheets("Cycle 2").Range("G7:G15").Select

Help??

P.S. Can you make it not specify which cells have no values? Just something like "Column G has incomplete data" :biggrin:


Thank you so much.
 
Upvote 0
OK:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range, Msg, Style, Title, Response, CellRef
CellRef = 0
Sheets("CCA Cyle").Select
Range("G7:G15").Select
For Each cell In Selection
If cell.Text = "" Then
CellRef = 1
GoTo Finish
End If
Next cell

Finish:
If CellRef = 1 Then
Msg = "Column G has incomplete data. Do you want to continue?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbNo Then
Cancel = True
Range("G1").Select
Exit Sub
End If
End If
Range("G1").Select
End Sub

[EDIT]: Added an "end if" that was missing
 
Upvote 0
Oh, i didnt mean to require all the cells in Range G7 to G15 to have a value. If any cell within that range has a value, message should no longer appear.
Sorry I wasnt clear! :)

can you provide updated code?

But you got the part about not requiring the validation in the other sheets already. Im so hats off to you!

Im excited!!! haha :)

Thanks!
 
Upvote 0
We're getting close:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("CCA Cyle").Select
If Application.WorksheetFunction.Sum(Range("g7:G15")) = 0 Then
Msg = "Column G has incomplete data. Do you want to continue?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbNo Then
Cancel = True
Range("G1").Select
Exit Sub
End If
End If
Range("G1").Select
End Sub
 
Upvote 0
We're getting close:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("CCA Cyle").Select
If Application.WorksheetFunction.Sum(Range("g7:G15")) = 0 Then
Msg = "Column G has incomplete data. Do you want to continue?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbNo Then
Cancel = True
Range("G1").Select
Exit Sub
End If
End If
Range("G1").Select
End Sub
 
Upvote 0
oops, i found two things that went wrong.

first, i placed values in all of G7 to G15 of CCA Cycle worksheet, i still got the error message when i shouldnt have.

second, i placed a value in G8 only of CCA Cycle worksheet, i still got an error message when i shouldnt have.

I was thinking, I dont know if this suggestion will make it easier or worse:

To address the second issue, How about setting a condition that if either column A or B in rows 7 to 15 has any value, then there must be a value in G corresponding to that row?

Example:
A7=no value
B7=with value
Warning message appears

A8=with value
B8=no value
Warning message appears

A15=no value
B15=no value
No warning message.

Still, the relevant rows are 7 to 15, and message will appear just once even when several instances have been found.

How about it? :biggrin: Or am I making your life difficult?
 
Upvote 0
Ok, if you want it that way:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Msg, Style, Title, Response, CellRef
CellRef = 0
Sheets("CCA Cyle").Select
[a7].Select
Do Until ActiveCell.Row = 16
If Len(ActiveCell.Value) > 0 Or Len(ActiveCell.Offset(0, 1).Value) > 0 Then
If ActiveCell.Offset(0, 6) = 0 Then
Msg = "Column G has incomplete data. Do you want to continue?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbNo Then
Cancel = True
Range("G1").Select
Exit Sub
End If
End If
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Hope we got it (lol).
 
Upvote 0
Hey you got it! That was great! :biggrin: :biggrin: :biggrin:
Thank you so much.

P.S. Now that im learning (?), I was wondering how the code will look like if, say, instead of checking if A or B column has value in rows 7 to 15, how about C and D columns instead? Just checking!
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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