Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeClose running twice
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeClose running twice
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeClose running twice
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeClose running twice
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeClose running twice
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeClose running twice
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeClose running twice
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeClose running twice
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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeClose running twice
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" wrote in message ... 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeClose running twice
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
beforeclose placement | Excel Discussion (Misc queries) | |||
EnableEvents BeforeClose | Excel Programming | |||
BeforeClose Problem | Excel Programming | |||
beforesave and beforeclose | Excel Programming | |||
BeforeClose Dilemma | Excel Programming |