Preventing Closure of a Workbook 2

[mattygriff]

I have a workbook which must not be closed if a specific cell (named CERTIFICATE_NO) is empty. Currently, I have this code.....

Private Sub workbook_onclose(cancel As Boolean)

    If Sheet3.Range("CERTIFICATE_NO").Value = "" Then
    
        MsgBox "Please enter a Certificate Number."
              
'        Sheet3.Unprotect
        Sheet3.Activate
        Sheet3.Range("CERTIFICATE_NO").Activate
'        Sheet3.Protect
    
    End If
            
End Sub

.....which tests the cell and, if it is empty, places the focus on it. The problem is that it doesn't stop the closure process which I need to do.

Can anyone help?

Thanks in advance.

 

[HarleyQuinn]

Try:

Cancel = True

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[mattygriff]

Thanks - I assume you mean

workbook_onclose(Cancel = True)

?

 

[PHV]

Private Sub workbook_onclose(cancel As Boolean)
    If Sheet3.Range("CERTIFICATE_NO").Value = "" Then
        MsgBox "Please enter a Certificate Number."
        [!]Cancel = True[/!]
        Sheet3.Activate
        Sheet3.Range("CERTIFICATE_NO").Activate
    End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

Almost:

Private Sub workbook_onclose(cancel As Boolean)

    If Sheet3.Range("CERTIFICATE_NO").Value = "" Then
    
        MsgBox "Please enter a Certificate Number."
              
'        Sheet3.Unprotect
        Sheet3.Activate
        Sheet3.Range("CERTIFICATE_NO").Activate
'        Sheet3.Protect
        [red]Cancel = True[/red]
    End If
            
End Sub

Cancel is a boolean variable effectively letting you cancel the rest of that event (in this case, stop the workbook closing).

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[DaveInIowa]

Is workbook_onclose a new event in Excel 2007, or did you mean Workbook_BeforeClose? Either case you would place the Cancel in the subroutine as PHV and HarleyQuinn advised.

 

[mattygriff]

Many thanks - works a treat.

 

[mattygriff]

Well spotted, Dave - I meant BeforeClose!

 

[combo]

The proper event procedure is Workbook_BeforeClose, in thisworkbook module. 'Cancel' is a parameter that tells excel if you need to cancel this event. Set it to 'True' in the middle of procedure after testing that the workbook should stay opened.

combo