I have the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
msg = "You are attempting to manually exit a DCA file." & vbCrLf &
vbCrLf
msg = msg + "This file type should only be closed through the DCA
menu. Failure to do so risks the LOSS of data!" & vbCrLf & vbCrLf & vbCrLf
msg = msg + "Press 'Ok' to Continue" & vbCrLf & vbCrLf
msg = msg + "Press 'Cancel' to abort the file save and return to
excel." & vbCrLf & vbCrLf
Ret = MsgBox(msg, vbExclamation + vbOKCancel)
If Ret = vbOK Then
' Proceed with file closure
ElseIf Ret = vbCancel Then
Cancel = True
End If
End Sub

If I hit Cancel I get the expected result, if I hit Ok, runs through to the
End Sub and goes back up to the beginning and runs the whole macro again. So
you effectively get asked twice on closing the file.

What am I doing wrong? Any ideas welcome, thanks.

--
Trefor

Try disabling event upon entry and the re-enabling on exit.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Trefor" wrote in message
...
I have the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
msg = "You are attempting to manually exit a DCA file." & vbCrLf &
vbCrLf
msg = msg + "This file type should only be closed through the DCA
menu. Failure to do so risks the LOSS of data!" & vbCrLf & vbCrLf & vbCrLf
msg = msg + "Press 'Ok' to Continue" & vbCrLf & vbCrLf
msg = msg + "Press 'Cancel' to abort the file save and return to
excel." & vbCrLf & vbCrLf
Ret = MsgBox(msg, vbExclamation + vbOKCancel)
If Ret = vbOK Then
' Proceed with file closure
ElseIf Ret = vbCancel Then
Cancel = True
End If
End Sub

If I hit Cancel I get the expected result, if I hit Ok, runs through to
the
End Sub and goes back up to the beginning and runs the whole macro again.
So
you effectively get asked twice on closing the file.

What am I doing wrong? Any ideas welcome, thanks.

--
Trefor

Bob,

Thanks for the tip, sounded good to me, but no luck.

--
Trefor


"Bob Phillips" wrote:

Try disabling event upon entry and the re-enabling on exit.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Trefor" wrote in message
...
I have the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
msg = "You are attempting to manually exit a DCA file." & vbCrLf &
vbCrLf
msg = msg + "This file type should only be closed through the DCA
menu. Failure to do so risks the LOSS of data!" & vbCrLf & vbCrLf & vbCrLf
msg = msg + "Press 'Ok' to Continue" & vbCrLf & vbCrLf
msg = msg + "Press 'Cancel' to abort the file save and return to
excel." & vbCrLf & vbCrLf
Ret = MsgBox(msg, vbExclamation + vbOKCancel)
If Ret = vbOK Then
' Proceed with file closure
ElseIf Ret = vbCancel Then
Cancel = True
End If
End Sub

If I hit Cancel I get the expected result, if I hit Ok, runs through to
the
End Sub and goes back up to the beginning and runs the whole macro again.
So
you effectively get asked twice on closing the file.

What am I doing wrong? Any ideas welcome, thanks.

--
Trefor

I even tried setting a variable to track that it had already made one pass,
but at the start of the second pass the variable got set to Empty and I had
nothing to check.

--
Trefor


"Trefor" wrote:

Bob,

Thanks for the tip, sounded good to me, but no luck.

--
Trefor


"Bob Phillips" wrote:

Try disabling event upon entry and the re-enabling on exit.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Trefor" wrote in message
...
I have the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
msg = "You are attempting to manually exit a DCA file." & vbCrLf &
vbCrLf
msg = msg + "This file type should only be closed through the DCA
menu. Failure to do so risks the LOSS of data!" & vbCrLf & vbCrLf & vbCrLf
msg = msg + "Press 'Ok' to Continue" & vbCrLf & vbCrLf
msg = msg + "Press 'Cancel' to abort the file save and return to
excel." & vbCrLf & vbCrLf
Ret = MsgBox(msg, vbExclamation + vbOKCancel)
If Ret = vbOK Then
' Proceed with file closure
ElseIf Ret = vbCancel Then
Cancel = True
End If
End Sub

If I hit Cancel I get the expected result, if I hit Ok, runs through to
the
End Sub and goes back up to the beginning and runs the whole macro again.
So
you effectively get asked twice on closing the file.

What am I doing wrong? Any ideas welcome, thanks.

--
Trefor

It only passes through once for me, so there must be some other code
interacting. What is in the OK action?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Trefor" wrote in message
...
I even tried setting a variable to track that it had already made one pass,
but at the start of the second pass the variable got set to Empty and I
had
nothing to check.

--
Trefor


"Trefor" wrote:

Bob,

Thanks for the tip, sounded good to me, but no luck.

--
Trefor


"Bob Phillips" wrote:

Try disabling event upon entry and the re-enabling on exit.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Trefor" wrote in message
...
I have the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
msg = "You are attempting to manually exit a DCA file." &
vbCrLf &
vbCrLf
msg = msg + "This file type should only be closed through the
DCA
menu. Failure to do so risks the LOSS of data!" & vbCrLf & vbCrLf &
vbCrLf
msg = msg + "Press 'Ok' to Continue" & vbCrLf & vbCrLf
msg = msg + "Press 'Cancel' to abort the file save and return
to
excel." & vbCrLf & vbCrLf
Ret = MsgBox(msg, vbExclamation + vbOKCancel)
If Ret = vbOK Then
' Proceed with file closure
ElseIf Ret = vbCancel Then
Cancel = True
End If
End Sub

If I hit Cancel I get the expected result, if I hit Ok, runs through
to
the
End Sub and goes back up to the beginning and runs the whole macro
again.
So
you effectively get asked twice on closing the file.

What am I doing wrong? Any ideas welcome, thanks.

--
Trefor

Bob,

Sorry what do you mean by the OK action?

--
Trefor


"Bob Phillips" wrote:

It only passes through once for me, so there must be some other code
interacting. What is in the OK action?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Trefor" wrote in message
...
I even tried setting a variable to track that it had already made one pass,
but at the start of the second pass the variable got set to Empty and I
had
nothing to check.

--
Trefor


"Trefor" wrote:

Bob,

Thanks for the tip, sounded good to me, but no luck.

--
Trefor


"Bob Phillips" wrote:

Try disabling event upon entry and the re-enabling on exit.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Trefor" wrote in message
...
I have the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
msg = "You are attempting to manually exit a DCA file." &
vbCrLf &
vbCrLf
msg = msg + "This file type should only be closed through the
DCA
menu. Failure to do so risks the LOSS of data!" & vbCrLf & vbCrLf &
vbCrLf
msg = msg + "Press 'Ok' to Continue" & vbCrLf & vbCrLf
msg = msg + "Press 'Cancel' to abort the file save and return
to
excel." & vbCrLf & vbCrLf
Ret = MsgBox(msg, vbExclamation + vbOKCancel)
If Ret = vbOK Then
' Proceed with file closure
ElseIf Ret = vbCancel Then
Cancel = True
End If
End Sub

If I hit Cancel I get the expected result, if I hit Ok, runs through
to
the
End Sub and goes back up to the beginning and runs the whole macro
again.
So
you effectively get asked twice on closing the file.

What am I doing wrong? Any ideas welcome, thanks.

--
Trefor