Prompt for Action before closing workbook
 

Going to the well again.

I've recorded a data archiving macro. I would like to ensure that the User
doesn't forget to go through the archiving procedure before shutting down the
Workbook.

Is there a simple macro to prompt for an action before the user shuts down
the workbook (would assume this would work if they attempted to close Excel,
while the workbook was still open)?

Thanks again.

Prompt for Action before closing workbook
 

You could use a Before Close event handler (copy into the Thisworkbook module
in the VBA editor). If the workbook is not archived the workbook remains
open by setting Cancel to true. If it is acrchived, the workbook is closed
(and the saved property is set to true so excel will not ask if you want to
save changes - change this part of the If construct as needed).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Response As Long
Response = MsgBox("Did you archive the workbook?", vbYesNo)
If Response = vbNo Then
Cancel = True
Else: ThisWorkbook.Saved = True
End If
End Sub


"bman342" wrote:

Going to the well again.

I've recorded a data archiving macro. I would like to ensure that the User
doesn't forget to go through the archiving procedure before shutting down the
Workbook.

Is there a simple macro to prompt for an action before the user shuts down
the workbook (would assume this would work if they attempted to close Excel,
while the workbook was still open)?

Thanks again.

Prompt for Action before closing workbook
 

Works great, thanks.

"JMB" wrote:

You could use a Before Close event handler (copy into the Thisworkbook module
in the VBA editor). If the workbook is not archived the workbook remains
open by setting Cancel to true. If it is acrchived, the workbook is closed
(and the saved property is set to true so excel will not ask if you want to
save changes - change this part of the If construct as needed).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Response As Long
Response = MsgBox("Did you archive the workbook?", vbYesNo)
If Response = vbNo Then
Cancel = True
Else: ThisWorkbook.Saved = True
End If
End Sub


"bman342" wrote:

Going to the well again.

I've recorded a data archiving macro. I would like to ensure that the User
doesn't forget to go through the archiving procedure before shutting down the
Workbook.

Is there a simple macro to prompt for an action before the user shuts down
the workbook (would assume this would work if they attempted to close Excel,
while the workbook was still open)?

Thanks again.

Prompt for Action before closing workbook
 

Woops. The data I was archiving was only a small subset of the data in the
entire workbook (I guess I led you to believe that since I was archiving the
data, I didn't need to save the workbook(?)). So "thisworkbook.saved = true"
was causing me to lose changes. No big problem though.

-b

"JMB" wrote:

You could use a Before Close event handler (copy into the Thisworkbook module
in the VBA editor). If the workbook is not archived the workbook remains
open by setting Cancel to true. If it is acrchived, the workbook is closed
(and the saved property is set to true so excel will not ask if you want to
save changes - change this part of the If construct as needed).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Response As Long
Response = MsgBox("Did you archive the workbook?", vbYesNo)
If Response = vbNo Then
Cancel = True
Else: ThisWorkbook.Saved = True
End If
End Sub


"bman342" wrote:

Going to the well again.

I've recorded a data archiving macro. I would like to ensure that the User
doesn't forget to go through the archiving procedure before shutting down the
Workbook.

Is there a simple macro to prompt for an action before the user shuts down
the workbook (would assume this would work if they attempted to close Excel,
while the workbook was still open)?

Thanks again.

Prompt for Action before closing workbook
 

Sorry about your loss, however, I did point that out.

"bman342" wrote:

Woops. The data I was archiving was only a small subset of the data in the
entire workbook (I guess I led you to believe that since I was archiving the
data, I didn't need to save the workbook(?)). So "thisworkbook.saved = true"
was causing me to lose changes. No big problem though.

-b

"JMB" wrote:

You could use a Before Close event handler (copy into the Thisworkbook module
in the VBA editor). If the workbook is not archived the workbook remains
open by setting Cancel to true. If it is acrchived, the workbook is closed
(and the saved property is set to true so excel will not ask if you want to
save changes - change this part of the If construct as needed).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Response As Long
Response = MsgBox("Did you archive the workbook?", vbYesNo)
If Response = vbNo Then
Cancel = True
Else: ThisWorkbook.Saved = True
End If
End Sub


"bman342" wrote:

Going to the well again.

I've recorded a data archiving macro. I would like to ensure that the User
doesn't forget to go through the archiving procedure before shutting down the
Workbook.

Is there a simple macro to prompt for an action before the user shuts down
the workbook (would assume this would work if they attempted to close Excel,
while the workbook was still open)?

Thanks again.