Solved: User Must Fill Certain Cells before Closing

[ROK]

Sorry folks I'm new to this and trying to figure out the forum rules but hope you can help and that I have enough info.
I have an excel doc and I'm trying to control data entry and make fields required under certain conditions. So, if you select any part of the range A5:I5 (A4:I4 is a list of headings) you must complete all fields before being able to close the doc. but if you start the next row range A6:I6 again you must complete fully and any other row you begin until you have all updates complete. Any help would be greatly appreciated.
This is the code I edited to try and suit but it makes all the cells in the range (Row) required even if the whole row is blank and I also need to hardcode all the rows to the range which potentially might be allot. I might be going the wrong way around this so any advice is greatly appreciated.

[VBA]
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Start As Boolean
Dim Rng1 As Range, Rng3 As Range, Rng4 As Range

Dim Prompt As String, RngStr As String
Dim Cell As Range
'set your ranges here
'Rng1 is on sheet "Group Profile" and cells B5 through B14
'Cell F1, A range of F5 through F7 etc. you can change these to
'suit your needs.
Set Rng1 = Sheets("Compliments").Range("A5:I5,A6:I6,A7:I7,A8:I8,A9:I9,A10:I10")
Set Rng3 = Sheets("Complaints").Range("A6:V6,A7:V7,A8:V8,A9:V9,A10:V10,A11:V11")
Set Rng4 = Sheets("Refunds").Range("A6:F6,A7:F7,A8:F8,A9:F9,A10:F10,A11:F11")
'message is returned if there are blank cells
Prompt = "Please check your data ensuring all required " & _
"cells are complete." & vbCrLf & "you will not be able " & _
"to close or save the workbook until the form has been filled " & _
"out completely. " & vbCrLf & vbCrLf & _
"The following cells are incomplete and have been highlighted yellow:" _
& vbCrLf & vbCrLf
Start = True
'highlights the blank cells
For Each Cell In Rng1
If Cell.Value = vbNullString Then
Cell.Interior.ColorIndex = 6 '** color yellow
If Start Then RngStr = RngStr & Cell.Parent.Name & vbCrLf
Start = False
RngStr = RngStr & Cell.Address(False, False) & ", "
Else
Cell.Interior.ColorIndex = 0 '** no color
End If
Next
If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
Start = True
If RngStr <> "" Then RngStr = RngStr & vbCrLf & vbCrLf
For Each Cell In Rng3
If Cell.Value = vbNullString Then
Cell.Interior.ColorIndex = 6 '** color yellow
If Start Then RngStr = RngStr & Cell.Parent.Name & vbCrLf
Start = False
RngStr = RngStr & Cell.Address(False, False) & ", "
Else
Cell.Interior.ColorIndex = 0 '** no color
End If
Next
If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
Start = True
If RngStr <> "" Then RngStr = RngStr & vbCrLf & vbCrLf
For Each Cell In Rng4
If Cell.Value = vbNullString Then
Cell.Interior.ColorIndex = 6 '** color yellow
If Start Then RngStr = RngStr & Cell.Parent.Name & vbCrLf
Start = False
RngStr = RngStr & Cell.Address(False, False) & ", "
Else
Cell.Interior.ColorIndex = 0 '** no color
End If
Next
If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
If RngStr <> "" Then
MsgBox Prompt & RngStr, vbCritical, "Incomplete Data"
Cancel = True
Else
'saves the changes before closing
ThisWorkbook.Save
Cancel = False
End If

Set Rng1 = Nothing
Set Rng3 = Nothing
Set Rng4 = Nothing

End Sub[/VBA]

[Aussiebear]

I think your logic is wrong here

[vba]For Each Cell In Rng1
If Cell.Value = vbNullString Then
Cell.Interior.ColorIndex = 6 '** color yellow[/vba]
since it opens up the potential for any one of the 54 cells in rng1 (A5:I10) to turn yellow if blank. When in actual fact, from my understanding of your post, you are trying to check if any cell in a row within the range has a value then the rest of the cells within the row must be completed.

Its 2.40 am here and its been a huge day at golf with the boys and I should be sleeping, but if this were my project, I'd create a form to enter the data into the worksheet. This way I could control the data better. So the logic would then follow the set of rules
If all nine textbox values were blank then the form could be closed,else all nine textbox values should be filled.

[ROK]

Aussiebear,

Nice one with the day of golf, coming to the end of our summer and weather has been so bad only got out for about 4 games in 5 months, wash out again this year.
I kind of thought everything was leading to User forms alright but as I use access mainly I never really needed them so I’m not really up to speed on them. I thought when I first looked at the problem that if I just had an if statement of if a5:i5 is not null then can not close, and then loop or always make sure the bottom row +1 is always blank before closing. Thanks a million for your help and direction on this I think I'll review my project direction and try to study up on forms, Legend thanks bud.

[Aussiebear]

Just another thought about this.... What was your intention if the 5 rows were full and you needed to add more data?

[ROK]

ya thats the main problem I had, trying to loop code to move to a new row everytime, the Userform seems to be the best option for the project and forget about trying to create some code to cover every possibility. I think I'll put a button in to add Record on each worksheet and have a form for the 3 worksheets which will fully controll data entry and then add that info to the each row. Thanks for the help Aussiebear

[ROK]

Created a simple user form which has sorted everything and is the best aproach for solving this problem. Thanks all