Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 21st 19, 07:57 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2019
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

I have found code to prevent closure of an application by clicking on the Red X. I placed it on Thisworkbook & it prevents the user from closing by clicking on the Red X.
Here is the code as written at present:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If CloseMode = 0 Then
Cancel = True
MsgBox "The X is disabled, please click on the Exit button.", vbCritical
End If
End Sub

This code is also preventing closure of the workbook/application. I think that once the code runs it is not released, I have not found a solution.
I see the code working this way. The user clicks on the X & the above message, pops up & that is the end of the process. The user clicks on Exit & it closes. I do have working code to close the workbook/application. However when the Exit button is clicked at present it goes back to the above code.
Something weird is going on . I went back in to the application, Clicked the X, & the message popped up once, & waited for me to respond.

Last edited by FrancisM : June 25th 19 at 12:38 PM Reason: Recent change in operation

  #2   Report Post  
Old June 23rd 19, 03:28 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,058
Default Using the red X (Upper right hand corner) for another purpose.

I have found code to prevent closure of an application by clicking on
the Red X. I placed it on Thisworkbook & it prevents the user from
closing by clicking on the Red X.
Here is the code as written at present:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If CloseMode = 0 Then
Cancel = True
MsgBox "The X is disabled, please click on the Exit button.",
vbCritical
End If
End Sub

This code is also preventing closure of the workbook/application. I
think that once the code runs it is not released, I have not found a
solution.
I see the code working this way. The user clicks on the X & the above
message, pops up & that is the end of the process. The user clicks on
Exit & it closes. I do have working code to close the
workbook/application. However when the Exit button is clicked at present
it goes back to the above code.


Why not just redirect to Call the process fired by the Exit button -OR- Call
Exit button's _Click event so any closing forces your Exit button procedure to
run???

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Old June 24th 19, 12:48 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2019
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

Thank you. I did not think of that. The code works, however, 2 things occur. 1). The msgBox occurs 2x. 2) The workbook closes, but not the application. The same thing happens when the exit button is clicked & call "Exit_Referrals" is present. If "Call Exit_Referrals" is not present, the msgbox appears 1x, and both the workbook & application close without any problem. I have tried to find a solution, but have not been successful.
Here is the current code:


Option Explicit

Sub Exit_Referrals()
Dim MsgBoxResult As Long

MsgBoxResult = MsgBox("Would you like to Exit the Referral Workbook?" & vbCr, _
vbYesNo, "Voc. Rehab. - Referral")
If MsgBoxResult = vbNo Then
Exit Sub
ElseIf MsgBoxResult = vbYes Then
Sheets("TOC").Select
Application.Calculation = xlCalculationAutomatic
Application.Quit
ThisWorkbook.Close SaveChanges:=True
End If
End Sub


When I moved "Application.Quit" after close, the workbook closed, but not the application.

Last edited by FrancisM : June 24th 19 at 01:01 PM Reason: Additional Info
  #4   Report Post  
Old June 24th 19, 01:20 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2019
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose

1 problem solved. I changed the code by adding "ThisWorkbook.Saved = True" after "Application.Quit". Now if I click on the exit button, both the workbook & the application close.
Here are the remaining problems: 1) Msg box appearing 2X, 2) when the X is clicked, msg box appearing 2x, & workbook closing not application.
  #5   Report Post  
Old June 24th 19, 06:52 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,058
Default Using the red X (Upper right hand corner) for another purpose.

1 problem solved. I changed the code by adding "ThisWorkbook.Saved =
True" after "Application.Quit". Now if I click on the exit button, both
the workbook & the application close.
Here are the remaining problems: 1) Msg box appearing 2X, 2) when the X
is clicked, msg box appearing 2x, & workbook closing not application.


Is the Referrals workbook the only one open?
Is your calling procedure in its _BeforeClose event? If so post that code!
Where is the Exit_Referrals sub located?
Why are you NOT using a global variable to track if the MsgBox result has
already been trapped?
Also, why do you add vbCr to your message Prompt?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #6   Report Post  
Old June 24th 19, 08:08 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2019
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

Ok. Here are the answers to your questions.
Is the Referrals workbook the only one open? - Yes
Where is the Exit_Referrals sub located? - I t resides in module 1
Why do you add vbCr to your message Prompt? - To start a new line in the message. (Open to suggestions)
Here is the before close code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Part of code to call a pop up calendar.
On Error Resume Next
Application.OnKey "+^{C}"
Application.CommandBars("Cell").Controls("Insert Date").Delete
Call Exit_Referrals
End Sub

I compared the original post to what I currently have, & noticed a big difference. I am SO SORRY. Here is the current code:

Option Explicit

Sub Exit_Referrals()
Dim MsgBoxResult As String
If MsgBox("Would you like to Exit the Referral Workbook?" & vbCr, vbYesNo, "Voc. Rehab. - Referral") = vbNo Then
Exit Sub
Else
Sheets("TOC").Select
Application.Calculation = xlCalculationAutomatic
ThisWorkbook.Close SaveChanges:=True
Application.Quit
End If
End Sub
Regarding "Why are you NOT using a global variable to track if the MsgBox result has already been trapped?" _ I did not know about global variable.
  #7   Report Post  
Old June 24th 19, 09:16 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,058
Default Using the red X (Upper right hand corner) for another purpose.

Ok. Here are the answers to your questions.
Is the Referrals workbook the only one open? - Yes
Where is the Exit_Referrals sub located? - I t resides in module 1
Why do you add vbCr to your message Prompt? - To start a new line in
the message. (Open to suggestions)
Here is the before close code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Part of code to call a pop up calendar.
On Error Resume Next
Application.OnKey "+^{C}"
Application.CommandBars("Cell").Controls("Insert Date").Delete
Call Exit_Referrals
End Sub

I compared the original post to what I currently have, & noticed a big
difference. I am SO SORRY. Here is the current code:

Option Explicit

Sub Exit_Referrals()
Dim MsgBoxResult As String
If MsgBox("Would you like to Exit the Referral Workbook?" & vbCr,
vbYesNo, "Voc. Rehab. - Referral") = vbNo Then
Exit Sub
Else
Sheets("TOC").Select
Application.Calculation = xlCalculationAutomatic
ThisWorkbook.Close SaveChanges:=True
Application.Quit
End If
End Sub
Regarding "Why are you NOT using a global variable to track if the
MsgBox result has already been trapped?" _ I did not know about global
variable.


Ok, that helps!

If you close the workbook itself -OR- if you quit Excel while the workbook is
open the workbook's _BeforeClose event fires. Other things are already in place
for it to send keystrokes, and delete a custom menu added to the Cells
commandbar. Where's the code to set this up?

Better:
Can you post a download link where I can retrieve a copy of the Referrals
workbook with 'dummy data' and detailed comments about what it does? (Assumes
this workbook performs task-specific processes that are fiscal period related)
If so, I'll review/revise it for optimal efficiency and post a link for
download.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #8   Report Post  
Old June 25th 19, 12:23 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2019
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

Thank You. I will provide a sample later today. I do have an update. Yesterday, when I clicked on the x, it would not close both the workbook & the application, Now it does. Ideally, when the x is clicked it takes the user to the exit button but does not close. Also when the exit button is clicked the message pops up twice. Here is a copy of the current "Exit_Referrals" code:

Option Explicit

Sub Exit_Referrals()
Dim MsgBoxResult As String
If MsgBox("Would you like to Exit the Referral Workbook?" & vbCr, vbYesNo, "Voc. Rehab. - Referral") = vbNo Then
Exit Sub
Else
Sheets("TOC").Select
Application.Calculation = xlCalculationAutomatic
ThisWorkbook.Save
Application.Quit
End If

End Sub
  #9   Report Post  
Old June 25th 19, 03:54 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2019
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

I will not be posting a sample till tomorrow. I have been diligently banging away at the code, & I believe that I am close to a solution.
  #10   Report Post  
Old June 26th 19, 06:32 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2019
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

Ok. I have gone from 4 problems to 1. Here is what currently happening.
When the application X is checked; a msgbox pops up instructing the user to "Click Exit to Close." When "Ok" or the X is checked on the msgbox, it disappears; That is what I want.
When the "Exit" button is checked, the following error message appears: "Compile error: Statements and labels invalid between Select Case and first case". It hangs on this line: "ThisWorkbook.fMacro = True" & on ".fMacro = True" in particular.

Please tell me why it is hanging on the line located in module 1 only & not on the line located in Thisworkbook. I commented out the 2 lines (fmacro), so I could close the file. If I did not post the link to the sample correctly, please let me know.

If you have other suggestions, please let me know.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hyperlink to cell and have it appear at top upper left hand corner Bisonhawk New Users to Excel 5 December 4th 08 08:16 PM
Moving Active Cell to Upper Left Hand Corner of Screen RJB Excel Programming 2 March 29th 07 08:18 PM
The 'X' in the upper right hand corner is suddenly dithered? Help Vicki P Excel Discussion (Misc queries) 3 May 13th 06 12:41 AM
What does a small green triangle in the upper left hand corner of. Fleas32 Excel Discussion (Misc queries) 3 December 29th 04 07:07 PM


All times are GMT +1. The time now is 02:22 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017