Execl VBA: Workbook_BeforeClose Event

[robz2009]

I have created an Excel spreadsheet which calls a Stored Procedure which works fine but I want to put some additonal validation in the spreadsheet which will stop a user closing the spreadsheet if the procedure is still executing.

I am looking to utilise the "Private Sub Workbook_BeforeClose(Cancel As Boolean)" event but if I reference the ADODB.Command variable which was called in the original "Private Sub Workbook_Open()" I get the error "Run Time Error 424 Object Required".

I have tried to create a Boolean variable instead of referencing the ADODB.Command but it seems as if I try to reference anything from the original "Private Sub Workbook_Open()" event in the "Private Sub Workbook_BeforeClose(Cancel As Boolean)" it can't see it?

I have tried changing the Events to Public but this had no effect. Can this be programmed in VBA, I'd like put a prompt in the
"Private Sub Workbook_BeforeClose(Cancel As Boolean)" event which indicates to the user the stored procedure is still being executed and then determine how to proceed by their reponse.

 

[kjv1611]

It does sound like it's a Private/Public thing.

Have you tried just removing the Private altogether, not replacing with Public, but just deleting?

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

The user has no control over public/private for the before_close event. It is what it is.

The issue ism "...if I reference the ADODB.Command variable ..."

IT must be declared in a MODULE, not in the Workbook object, as a PUBLIC variable. In fact, I'd declare ALL the ADODB objects as PUBLIC variables.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robz2009]

Thanks as I have now created a module and declared the variables as Public,

Public con As ADODB.Connection
Public cmd As ADODB.Command
Public rs As ADODB.Recordset

This is now being picked up by my Workbook_BeforeClose event. I still have a couple of problems,

1. The event is Workbook_BeforeClose so specific to the workbook right? If a user closes the Excel instance ('X' in the outer window) this event is never picked up? It only triggers when I click the 'X' in the sub window of Excel i.e. the Workbook itself. I want this code to trigger regardless of how the user quits Excel.

2. This is the full code I have put in this event,

If cmd.State = adStateExecuting Then
If MsgBox("Are you sure you wish to quit?", vbYesNo)= vbNo Then
Cancel = True
Else
Set con = Nothing
Set cmd = Nothing
Set rs = Nothing
End If
End If

This message box displays when I click the 'X' of the workbook but if I select Yes and wish to quit and close the connections to the DB the spreadsheet just freezes and I have to forcefully kill the connection at the DB end.

If I haven't explained this well enough please let me know.

 

[kjv1611]

I'm not sure what the event is named, but you could put some conditional code in the application close/quit event if possible, and have it call this procedure if the activeworkbook is the workbook you're working on in this thread.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

It does no seem to matter whether you X the workbook or the application. In both instances, the before_close event fires.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]