Spreadsheet cannot close until certain cells are completed

[Airbisk]

Hi,

I have a spreadsheet that will be filled in by a host of users, currently there is data validation on most of the cells. When I tested the validation, it still allows the user to delete data that meets the criteria and then enter data that bypasses the validation.

What i wanted to add was some code that didn't allow the spreadsheet to be closed until the cells are filled in properly.

The spreadsheet acts like a register so each row is a new record and the data validation presently works on cells values in the row.

I have looked at some code trying to get it to work for me but I cannot seem to get it to work how I would like.

If you could help that would be appreciated, the code below shows only one IF statement but it will probably end with a few more. What I would like it to do also is for it to stop after the first error and maybe click into the cell and display a message of what cell is causing the issue. What it tends to do presently is run through all the errors first. I also have trouble with it closing, I don't want it to close without saving if changes have been made and also it sometimes doesn't close even when all criteria is met.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim TheRow As Integer

TheRow = 3

Do

TheRow = TheRow + 1

If TheRow = 4001 Then Exit Do

Checks whether a valid reason is entered when customer field is complete

If Cells(TheRow, 1).Value > 0 And _
Cells(TheRow, 2).Value = "xx) Issued from HO to Depot Mgr" Or _
Cells(TheRow, 2).Value = "05) Issued by Depot Mgr to Driver/Fitter" And _
Cells(TheRow, 4).Value > 0 And _
Cells(TheRow, 5).Value > 0 And _
Cells(TheRow, 6).Value > 0 Then _
MsgBox "Please enter a valid Reason!"


If vbOK Then
Cancel = True
End If

Loop

ActiveWorkbook.Close


End Sub

 

[combo]

1) check the brackets in condition - the order of evaluation of the condition is the same as in formula with 'And' equivalent to '*' and 'Or' equinalent to '+',
2) vbOK is the enumerated constant (VbMsgBoxResult) equals to 1, what is the logic of your second 'If' structure?
3) maybe it would be better to check the row immediately (either Worksheet_Change event or userform intermediate entry with validation before passing proper values to worksheet).


combo

 

[SkipVought]

Your code, does not give the user 1) the information regarding what row and column is deficient and 2) the loop continues relentlessly without giving the user a chance to make a correction.

You could build an array of error/location and lead the user to the deficient cells for correction.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}