Exit Sub on "Before Close" event

[wpryan]

Hi All,

I have some code that I run on the Before Close event, which does various things, including setting the file name of an output file created by the code. The file name is dependent on certain data entry in one of the worksheets. I need some code that will stop the Close event in case the fields are not complete. What I have is:

    If Sheets("Instructions").Range("C1") = "" Or Sheets("Instructions").Range("C2") = "" _
    Or Sheets("Instructions").Range("C3") = "" Or Sheets("Instructions").Range("C4") = "" Then
        MsgBox "Please complete required data (Date, Name, LDV Serial Number and Threshold Power", vbOKOnly
Exit Sub
    End If

However, the Exit Sub commands doesn't do what I want it to do, which is stop the entire Close event from happening. What am I missing...?

 

[Legacy 143009]

Try:

If Sheets("Instructions").Range("C1") = "" Or Sheets("Instructions").Range("C2") = "" _
    Or Sheets("Instructions").Range("C3") = "" Or Sheets("Instructions").Range("C4") = "" Then
  MsgBox "Please complete required data (Date, Name, LDV Serial Number and Threshold Power", vbOKOnly
  Cancel = True
End If

 

[dmt32]

Hi
see if this will do what you want

Standard module

Function IsComplete() As Boolean
    Dim rng As Range
    Set rng = Worksheets("Instructions").Range("C1:C4")
    IsComplete = Application.CountA(rng) = rng.Cells.Count
    If Not IsComplete Then
        MsgBox "Please complete required data (Date, Name, LDV Serial Number and Threshold Power", vbOKOnly
        rng.Parent.Activate
    End If
End Function

Thisworkbook code page

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = Not IsComplete
End Sub

Dave

 

[wpryan]

Hi
see if this will do what you want

Standard module

Function IsComplete() As Boolean
    Dim rng As Range
    Set rng = Worksheets("Instructions").Range("C1:C4")
    IsComplete = Application.CountA(rng) = rng.Cells.Count
    If Not IsComplete Then
        MsgBox "Please complete required data (Date, Name, LDV Serial Number and Threshold Power", vbOKOnly
        rng.Parent.Activate
    End If
End Function

Thisworkbook code page

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = Not IsComplete
End Sub

Dave

This looks like a more elegant solution. However, I'm not sure where the Function should be put...

 

[dmt32]

This looks like a more elegant solution. However, I'm not sure where the Function should be put...

As stated - "Standard Module"

Dave

 

[wpryan]

This looks like a more elegant solution. However, I'm not sure where the Function should be put...

I have a need for similar code in another place, however, the cells.count should be in a non-continuous range. Is that possible? I tried:

Dim wsData As Worksheet: Set wsData = Worksheets("Data")
Dim dRange As Range: Set dRange = wsData.Range("C10:C46, D5, G10:G46, H5, K10:K46, L5")

...but I'm getting an Application-Defined or object defined error...

thanks in advance...

 

[dmt32]

do not get that error

Dim wsData As Worksheet
Dim dRange As Range

Set wsData = Worksheets("Data")

Set dRange = wsData.Range("C10:C46, D5, G10:G46, H5, K10:K46, L5")

MsgBox dRange.Cells.Count

Dave

 

[wpryan]

do not get that error

Dim wsData As Worksheet
Dim dRange As Range

Set wsData = Worksheets("Data")

Set dRange = wsData.Range("C10:C46, D5, G10:G46, H5, K10:K46, L5")

MsgBox dRange.Cells.Count

Dave

...strange. after a computer restart I didn't get the error anymore...