I am in the process of creating an "exit menu" that triggers on the
"before_close" event on the workbook. The menu itself is fairly simple
offering 4 buttons:

1. Email spreadsheet
2. Save spreadsheet
3. Return to the worksheet
4. Exit the worksheet

The email, save, and exit buttons work fine. The trouble that I am having is
the "Return to worksheet" button.

I have tried the following code:

Private Sub ReturnButton_Click()
Cancel = True
Unload ExitOptions
Application.ExecuteExcel4Macro "HALT(True)"
End Sub

If I try to cancel the exit, the sheet still closes. I can't seem to get it
to return an halt the closing process.

I have tried quite a few permutations, but still nada,

Any suggestions?

WillRn

Will,

Cancel is not a global variable, but is local to the before_close event, so
you will need to set cancel to true within the event code itself.

HTH,
Bernie
MS Excel MVP


"WillRn" wrote in message
...
I am in the process of creating an "exit menu" that triggers on the
"before_close" event on the workbook. The menu itself is fairly simple
offering 4 buttons:

1. Email spreadsheet
2. Save spreadsheet
3. Return to the worksheet
4. Exit the worksheet

The email, save, and exit buttons work fine. The trouble that I am having
is
the "Return to worksheet" button.

I have tried the following code:

Private Sub ReturnButton_Click()
Cancel = True
Unload ExitOptions
Application.ExecuteExcel4Macro "HALT(True)"
End Sub

If I try to cancel the exit, the sheet still closes. I can't seem to get
it
to return an halt the closing process.

I have tried quite a few permutations, but still nada,

Any suggestions?

WillRn

Okay, that makes sense. But how do I use my custom menu and stay in the
before_close event so that cancel=true will work?


"Bernie Deitrick" wrote:

Will,

Cancel is not a global variable, but is local to the before_close event, so
you will need to set cancel to true within the event code itself.

HTH,
Bernie
MS Excel MVP


"WillRn" wrote in message
...
I am in the process of creating an "exit menu" that triggers on the
"before_close" event on the workbook. The menu itself is fairly simple
offering 4 buttons:

1. Email spreadsheet
2. Save spreadsheet
3. Return to the worksheet
4. Exit the worksheet

The email, save, and exit buttons work fine. The trouble that I am having
is
the "Return to worksheet" button.

I have tried the following code:

Private Sub ReturnButton_Click()
Cancel = True
Unload ExitOptions
Application.ExecuteExcel4Macro "HALT(True)"
End Sub

If I try to cancel the exit, the sheet still closes. I can't seem to get
it
to return an halt the closing process.

I have tried quite a few permutations, but still nada,

Any suggestions?

WillRn

Will,

You could declare a public global variable that your button click sets to
true:

At the top of a module
Public glblCancel As Boolean

Then in your sub that show the userform:

Sub TryNow()
glblCancel = False ' initialize
Load UserForm1
UserForm1.Show
'user interacts with the userform
MsgBox glblCancel ' this is set in the click event
End Sub

Private Sub ReturnButton_Click()
glblCancel = True
Unload ExitOptions
Application.ExecuteExcel4Macro "HALT(True)"
End Sub

Then in the before close event:

....
Cancel = glblCancel
.....

HTH,
Bernie
MS Excel MVP


"WillRn" wrote in message
...
Okay, that makes sense. But how do I use my custom menu and stay in the
before_close event so that cancel=true will work?


"Bernie Deitrick" wrote:

Will,

Cancel is not a global variable, but is local to the before_close event,
so
you will need to set cancel to true within the event code itself.

HTH,
Bernie
MS Excel MVP


"WillRn" wrote in message
...
I am in the process of creating an "exit menu" that triggers on the
"before_close" event on the workbook. The menu itself is fairly simple
offering 4 buttons:

1. Email spreadsheet
2. Save spreadsheet
3. Return to the worksheet
4. Exit the worksheet

The email, save, and exit buttons work fine. The trouble that I am
having
is
the "Return to worksheet" button.

I have tried the following code:

Private Sub ReturnButton_Click()
Cancel = True
Unload ExitOptions
Application.ExecuteExcel4Macro "HALT(True)"
End Sub

If I try to cancel the exit, the sheet still closes. I can't seem to
get
it
to return an halt the closing process.

I have tried quite a few permutations, but still nada,

Any suggestions?

WillRn

That worked,

Thanks a bunch!

"Bernie Deitrick" wrote:

Will,

You could declare a public global variable that your button click sets to
true:

At the top of a module
Public glblCancel As Boolean

Then in your sub that show the userform:

Sub TryNow()
glblCancel = False ' initialize
Load UserForm1
UserForm1.Show
'user interacts with the userform
MsgBox glblCancel ' this is set in the click event
End Sub

Private Sub ReturnButton_Click()
glblCancel = True
Unload ExitOptions
Application.ExecuteExcel4Macro "HALT(True)"
End Sub

Then in the before close event:

....
Cancel = glblCancel
.....

HTH,
Bernie
MS Excel MVP


"WillRn" wrote in message
...
Okay, that makes sense. But how do I use my custom menu and stay in the
before_close event so that cancel=true will work?


"Bernie Deitrick" wrote:

Will,

Cancel is not a global variable, but is local to the before_close event,
so
you will need to set cancel to true within the event code itself.

HTH,
Bernie
MS Excel MVP


"WillRn" wrote in message
...
I am in the process of creating an "exit menu" that triggers on the
"before_close" event on the workbook. The menu itself is fairly simple
offering 4 buttons:

1. Email spreadsheet
2. Save spreadsheet
3. Return to the worksheet
4. Exit the worksheet

The email, save, and exit buttons work fine. The trouble that I am
having
is
the "Return to worksheet" button.

I have tried the following code:

Private Sub ReturnButton_Click()
Cancel = True
Unload ExitOptions
Application.ExecuteExcel4Macro "HALT(True)"
End Sub

If I try to cancel the exit, the sheet still closes. I can't seem to
get
it
to return an halt the closing process.

I have tried quite a few permutations, but still nada,

Any suggestions?

WillRn