Prevent Excel Quitting if cell not compleet

[HUXLEYHOUND]

Hi,

I am stuck here.. I have a excel sheet with Values in Column B, C and T.

When data is enetered into a new row on Culimn B or C then T must be completed with value. If T is not complete I want the user to be warned when they try to save or exit the Workbook and prevent the save.

If there is no data in B or C then it is ok to quit excel.

Any ideas?


Huxley

 

[pbornemeier]

Welcome to the Board!

You can use the BeforeSave and BeforeClose Workbook Events to initiate a validation routine. If the users data fails the check, you can display an error messsage and set Cancel = True to stop the Save or Close.

This will not stop an annoyed user from terminating Excel via the Task Manager, Power Switch or Wall Plug or Sledge Hammer, so be sure the error message is clear on what the problem is and how to resolve it.

Are you validating each row via the SelectionChange or Change Worksheet Events?

 

[HUXLEYHOUND]

Hello,

Thank you foe the quick response. I have got this far from an thread in this forum:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("sheet1").Range("a1").Value = 10 Then Cancel = True
MsgBox "Please do something....."
End Sub

What I need to do is ensure column T has a value (text) from drop down list if Column B and C have a value. If B & C have a value and T is left blank then prevent save and prompt user that the vlaue in T is missing or incorrect. If B, C and T are present, then ok to exit?

I am making sense?


Huxley

 

[pbornemeier]

Could there be more than one row than filled in at a time?
If so, is there a range or rows that have to be validated?
If not, what row should be checked?

 

[HUXLEYHOUND]

Hi,

Sorry for the delay in replying..

In answer:

Could there be more than one row than filled in at a time?

Answer: Yes

If so, is there a range or rows that have to be validated?

Answer: Only those that have data in Column B & C. If there is nothing in the row for B & C then allow save.

I really appreciate this...If we can boss it it will ensure data is correct and upto date.

Huxley

 

[pbornemeier]

This code will check each worksheet in the workbook to ensure that there is no row with only 1 or 2 of Columns B, C & T filled in before allowing the book to be saved or closed.

Put this code in the ThisWorkbook codepage:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        If Not ColumnsBCT_OK Then
            MsgBox "There is at least one row in this worksheet that has only 1 or 2 of the cells in Columns B, C, T  filled in.  Correct this problem before saving the file."
            Cancel = True
            Exit For
        End If
    Next
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        If Not ColumnsBCT_OK Then
            MsgBox "There is at least one row in this worksheet that has only 1 or 2 of the cells in Columns B, C, T  filled in.  Correct this problem before saving the file."
            Cancel = True
            Exit For
        End If
    Next
End Sub

Insert a standard module and put this code in it:

Function ColumnsBCT_OK() As Boolean
    Dim lLastB As Long
    Dim lLastC As Long
    Dim lLastT As Long
    Dim lTCount As Long
    Dim lBCount As Long
    Dim lCCount As Long
    Dim bOK As Boolean
 
    lLastB = Cells(Rows.Count, "B").End(xlUp).Row
    lLastC = Cells(Rows.Count, "C").End(xlUp).Row
    lLastT = Cells(Rows.Count, "T").End(xlUp).Row
 
    lBCount = Application.WorksheetFunction.CountA(Range("B:B"))
    lCCount = Application.WorksheetFunction.CountA(Range("C:C"))
    lTCount = Application.WorksheetFunction.CountA(Range("T:T"))
 
    bOK = True
 
    If lLastB <> lLastC Then bOK = False: GoTo CheckbOK
    If lLastB <> lLastT Then bOK = False: GoTo CheckbOK
    If lBCount <> lTCount Then bOK = False: GoTo CheckbOK
    If lCCount <> lTCount Then bOK = False: GoTo CheckbOK
 
CheckbOK:
 
    If Not bOK Then
        ColumnsBCT_OK = False
    Else
        ColumnsBCT_OK = True
    End If
 
End Function

 

[xenou]

Try to solve the problem at its root. Why is the data being left incomplete? Is the information lacking? Are the employees not trained or the task not explained correctly? Is the spreadsheet set up poorly so that it's confusing or hard to see what needs to be done? Should the application be enhanced with better input forms?

Putting in data validation at the time of entry would make more sense than a workbook_close hack. Your employees will also feel better about being given the right tools for the job, rather than having a sense that someone is waiting for them to make a mistake.

 

[pbornemeier]

An input form that validated all entries for a row before writing/updating the appropriate row of the spreadsheet would be the preferred method of handling this and I should have offered that as a possibility, albeit a more complex one.

 

[HUXLEYHOUND]

Superb..works a treat..exactly what I wanted. Brilliant. Thank you. The data in this sheet is Business Critical and mistakes cannot be allowed for. We are a fast moving department and simple validation checks like this prevent mistakes. It can be used for a whole host of other sheets I have.

A big thank you..very pleased..

 

[pbornemeier]

I'm glad that validation works for you, but if you want a more robust solution, please answer the following:

What data (column titles) are included in each row? What rules should be enforced for each column? For each row?

It would be cleaner and more easily controlled to use a userform for repetitive data input/editing. It is more complex to set up, but easier to work in once it is set up.