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" <(E-Mail Removed)> wrote in message
news:E4EEFFF4-87E5-439A-B514-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:E4EEFFF4-87E5-439A-B514-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> > news:E4EEFFF4-87E5-439A-B514-(E-Mail Removed)...
> > >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" <(E-Mail Removed)> wrote in message
news:C56C2F74-6D45-444D-AC1B-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
>> > news:E4EEFFF4-87E5-439A-B514-(E-Mail Removed)...
>> > >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" <(E-Mail Removed)> wrote in message
> news:C56C2F74-6D45-444D-AC1B-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> >> > news:E4EEFFF4-87E5-439A-B514-(E-Mail Removed)...
> >> > >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
> >> > >
> >> >
> >> >
> >> >
>
>
>

Trefor,

I was referring to this bit


If Ret = vbOK Then
' Proceed with file closure
ElseIf Ret = vbCancel Then
Cancel = True
End If

I assumed there was some code in the vbOK path that you hadn't bothered to
include.

--
---
HTH

Bob


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



"Trefor" <(E-Mail Removed)> wrote in message
news:E2E01B7F-3624-4C8F-B2AD-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:C56C2F74-6D45-444D-AC1B-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
>> >> > news:E4EEFFF4-87E5-439A-B514-(E-Mail Removed)...
>> >> > >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, OK understand, but no there is nothing, just was i copied here. Is there
a reason why once set the variable reurns to Empty on the seond pass?

--
Trefor


"Bob Phillips" wrote:

> Trefor,
>
> I was referring to this bit
>
>
> If Ret = vbOK Then
> ' Proceed with file closure
> ElseIf Ret = vbCancel Then
> Cancel = True
> End If
>
> I assumed there was some code in the vbOK path that you hadn't bothered to
> include.
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
>
>
> "Trefor" <(E-Mail Removed)> wrote in message
> news:E2E01B7F-3624-4C8F-B2AD-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:C56C2F74-6D45-444D-AC1B-(E-Mail Removed)...
> >> >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" <(E-Mail Removed)> wrote in message
> >> >> > news:E4EEFFF4-87E5-439A-B514-(E-Mail Removed)...
> >> >> > >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
> >> >> > >
> >> >> >
> >> >> >
> >> >> >
> >>
> >>
> >>
>
>
>

Are you saying Ret is empty second time around?

--
---
HTH

Bob


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



"Trefor" <(E-Mail Removed)> wrote in message
news:792B5FC7-CBBC-4C1E-9955-(E-Mail Removed)...
> Bob, OK understand, but no there is nothing, just was i copied here. Is
> there
> a reason why once set the variable reurns to Empty on the seond pass?
>
> --
> Trefor
>
>
> "Bob Phillips" wrote:
>
>> Trefor,
>>
>> I was referring to this bit
>>
>>
>> If Ret = vbOK Then
>> ' Proceed with file closure
>> ElseIf Ret = vbCancel Then
>> Cancel = True
>> End If
>>
>> I assumed there was some code in the vbOK path that you hadn't bothered
>> to
>> include.
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>>
>>
>> "Trefor" <(E-Mail Removed)> wrote in message
>> news:E2E01B7F-3624-4C8F-B2AD-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:C56C2F74-6D45-444D-AC1B-(E-Mail Removed)...
>> >> >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" <(E-Mail Removed)> wrote in message
>> >> >> > news:E4EEFFF4-87E5-439A-B514-(E-Mail Removed)...
>> >> >> > >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 you help, I found my problem eventually. Seems I was get myself
in all knots over BeforeClose, BeforeSave and Auto_Close.

So on close it was going through the BeforeClose, falling through no issue,
then going through the Auto_Close hitting a Thisworkbook.close and going back
to the BeforeClose for a second run.

It took me hours to get the right combination of options because I wanted to
intercept both a "manual" save and close with a warning message and the
ability to abort or save/close if the users wants. BUT I also want to be able
to to save/close the file from within a macro without getting all these
messages. I head is still spinning, but I think I have it.

Thanks again.

--
Trefor


"Bob Phillips" wrote:

> Are you saying Ret is empty second time around?
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
>
>
> "Trefor" <(E-Mail Removed)> wrote in message
> news:792B5FC7-CBBC-4C1E-9955-(E-Mail Removed)...
> > Bob, OK understand, but no there is nothing, just was i copied here. Is
> > there
> > a reason why once set the variable reurns to Empty on the seond pass?
> >
> > --
> > Trefor
> >
> >
> > "Bob Phillips" wrote:
> >
> >> Trefor,
> >>
> >> I was referring to this bit
> >>
> >>
> >> If Ret = vbOK Then
> >> ' Proceed with file closure
> >> ElseIf Ret = vbCancel Then
> >> Cancel = True
> >> End If
> >>
> >> I assumed there was some code in the vbOK path that you hadn't bothered
> >> to
> >> include.
> >>
> >> --
> >> ---
> >> HTH
> >>
> >> Bob
> >>
> >>
> >> (there's no email, no snail mail, but somewhere should be gmail in my
> >> addy)
> >>
> >>
> >>
> >> "Trefor" <(E-Mail Removed)> wrote in message
> >> news:E2E01B7F-3624-4C8F-B2AD-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:C56C2F74-6D45-444D-AC1B-(E-Mail Removed)...
> >> >> >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" <(E-Mail Removed)> wrote in message
> >> >> >> > news:E4EEFFF4-87E5-439A-B514-(E-Mail Removed)...
> >> >> >> > >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
> >> >> >> > >
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>