Mandatory Field or unable to save based another field having data

[jingles9]

Hello Everyone
I have a database that I have created (with the welcomed help of others). In order to save the file I want to make cells mandatory based on data in another cell.
For example if someone adds information to cell C8 then B8 (todays date), E8 (FC Name) and K8 (Status) must have a value. I found this formula which works for B8 but I do not know how to modify it to include E8 and K8. I also needs this formula to repeat for the entire column....spreadsheet has 99 rows.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Not Sheets("Input").Range("C8") Is Nothing Then
If Sheets("Input").Range("B8") = vbNullString Then
MsgBox "You must enter todays date in Column B. Please Revise", vbOKOnly, "Oops"
Exit Sub
End If
End If

End Sub

...any help is greatly appreciated

[ARGK]

Hi
Try this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Not Sheets("Input").Range("C8") Is Nothing Then
If Sheets("Input").Range("B8") = vbNullString Then
MsgBox "You must enter todays date in Column B. Please Revise", vbOKOnly, "Oops"
If Sheets("Input").Range("E8") = vbNullString Then
MsgBox "You must enter data in Column E. Please Revise", vbOKOnly, "Oops"
If Sheets("Input").Range("K8") = vbNullString Then
MsgBox "You must enter data in Column K. Please Revise", vbOKOnly, "Oops"
Exit Sub
End If
End If
End Sub
Good luck.
Tony

[jingles9]

I tried the formula and it wouldn't work. When I adjusted it to the following it works but only for row 8. How do I adjust the formula to get it to read to the last row (row 99)?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Not Sheets("Input").Range("C8") Is Nothing Then
If Sheets("Input").Range("B8") = vbNullString Then
MsgBox "You must enter todays date in Column B. Please Revise", vbOKOnly, "Oops"
End If
End If

If Not Sheets("Input").Range("C8") Is Nothing Then
If Sheets("Input").Range("E8") = vbNullString Then
MsgBox "You must enter todays date in Column E. Please Revise", vbOKOnly, "Oops"
End If
End If

If Not Sheets("Input").Range("C8") Is Nothing Then
If Sheets("Input").Range("K8") = vbNullString Then
MsgBox "You must enter todays date in Column K. Please Revise", vbOKOnly, "Oops"

Exit Sub
End If
End If

End Sub

I have attached an updated version of the excel document.

[ARGK]

Hi
Have a look at this version. Sorry I have not had time to test it as I am off out shortly. Let me know how you get on.
If you have issues with the new code then please detail the exact criteria you need the code to perform step by step.
Good luck.
Tony

[jingles9]

Hi Tony
The only issue I have is that it is asking me to put a value in column B, E or K even when column C does not have a value.
I only need it to prompt if Column C has a value....and column B, E or K in the same row do not have a value.

[ARGK]

Hi
Try this version and let me know if it does what you need.
Good luck.
Tony

[jingles9]

Hi Tony
This version does not work either. Before it prompted for all fields whether Column C had a value or not, now it doesn't prompt at all

[ARGK]

Hi
This is strange because it works fine for me with the following criteria:
1. C not blank.
2. Columns B, E and K are blank.
Press Ctrl + s and you are prompted to ensure there is an entry in Columns B, E and K.
The only difference is that I am using Excel 2010 and you are using 2007 (which should not really make a difference).
However I have enclosed a 2003 version which will open in 2007 okay and "should work". I have saved it as a 2003 version simply because there is a known issues with saving any MS Office file to a 2007 version.
Let me know how you get on with this version.
Tony

[jingles9]

Hi Tony....This sheet does not work either. The first cell that should come up is E9, but the first one it prompts for is B10. It also doesn't save properly...it gives me 86 critical errors.

[ARGK]

Hi
The workbook works fine for me so this is strange. It works on the basis that if Col C is populated and Columns B, E and K do not, this is what it is doing. Do you need something different?
As for the errors I have no idea what is causing them. I suggest you copy the code and put it into your own version of the Workbook.
Tony

[jingles9]

Hi Tony
I tried copying the script into my spreadsheet. I resaved as 2007 so that solved the critical errors, but it still is not promting for the correct cells.
I want the following: If there is information in Column C, then there must be something in Column B, E And K. If Column B and/or E and/or K have nothing in them (and there is something in Column C), it should prompt the user to input something in that cell.
The formula in your last post is working if something is in Column C and nothing is in column b, but it is not prompting if nothing is in column E or K on the same row.

[ARGK]

Hi
I have attached a sample output from my end. Basically if there is an entry in Column C but NOTHING in Columns B, E or K the following message box will appear informing the user that there are missing entries and where those missing entries are. See sample.
Is this not what you need?
Tony

[jingles9]

Hi Tony, the message box works properly (in that it tells you what line is missing data), but if you look/play with the data in the spreadsheet, you will not get the message box if nothing is in column E or K and something is in Column C.
For example, in your screenshot there is data missing from cell E9, E10 and K10, yet it did not prompt for those cells. it seems to only prompt if column B is missing data.

[ARGK]

Hi
Try this code. You will need to move the data validation entries to a separate sheet or the code will keep asking you to enter values in Columns B, E and K right down to row 165.
Hope this helps.
Good luck.
Tony

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim count1 As Integer
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If

For count1 = 8 To LastRow
If Sheets("Input").Range("C" & count1) <> vbNullString Then
If Sheets("Input").Range("B" & count1) = vbNullString Then
MsgBox "You must enter todays date in Cell B" & count1 & ". Please Revise", vbOKOnly, "Oops"
Range("B" & count1).Select
GoTo Finished
End If
End If

If Sheets("Input").Range("C" & count1) <> vbNullString Then
If Sheets("Input").Range("E" & count1) = vbNullString Then
MsgBox "You must enter FC Name in Cell E" & count1 & ". Please Revise", vbOKOnly, "Oops"
Range("E" & count1).Select
GoTo Finished
End If
End If

If Sheets("Input").Range("C" & count1) <> vbNullString Then
If Sheets("Input").Range("K" & count1) = vbNullString Then
MsgBox "You must enter a Status in Cell K" & count1 & ". Please Revise", vbOKOnly, "Oops"
Range("K" & count1).Select
GoTo Finished
End If
End If
Next count1
Finished:
End Sub

[jingles9]

This worked great!!!! Thank you so much

[ARGK]

Thanks for the feedback. Glad we got there in the end.
Tony