Prevent Excel Quitting if cell not compleet

HUXLEYHOUND

New Member
Joined
Mar 10, 2011
Messages
4
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,882
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

New Member
Joined
Mar 10, 2011
Messages
4
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

Well-known Member
Joined
May 24, 2005
Messages
3,882
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

New Member
Joined
Mar 10, 2011
Messages
4

ADVERTISEMENT

Hi,

Sorry for the delay in replying..:biggrin:

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

Well-known Member
Joined
May 24, 2005
Messages
3,882
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:
Code:
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:
Code:
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 24, 2005
Messages
3,882
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

New Member
Joined
Mar 10, 2011
Messages
4
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

Well-known Member
Joined
May 24, 2005
Messages
3,882
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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
Top