Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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  
Junior Member
 
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  
Junior Member
 
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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  
Junior Member
 
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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  
Junior Member
 
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  
Junior Member
 
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  
Junior Member
 
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.


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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.


No download link to sample posted yet...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #12   Report Post  
Junior Member
 
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

Here is a link.


https://1drv.ms/x/s!Ak-4iXjPpsJMgSUd...t0LS1?e=WdHjTL
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Using the red X (Upper right hand corner) for another purpose.

Here is a link.


https://1drv.ms/x/s!Ak-4iXjPpsJMgSUd...t0LS1?e=WdHjTL


It says the workbook can't be opened online because it's over 5MB in size.
(Seems it has bloated, perhaps?) Don't want to open it online anyway so try
another way.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #14   Report Post  
Junior Member
 
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

Ok. I will take that file down, & post a new link shortly. That file had 68 sheets, but no data. The new file will have the same name & only 3 sheets.
  #15   Report Post  
Junior Member
 
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

Try this link:


https://1drv.ms/x/s!Ak-4iXjPpsJMgSbC...fYd-P?e=6JFhQo


  #16   Report Post  
Junior Member
 
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

I don't know if that link worked, so here is the current code:
This is located in ThisWorkbook & does work.


Public fMacro As Boolean
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
Dim Msg As String, Ans As Variant
'Cancels The Red X on both the Application & the "Click....." window.
If Not fMacro Then cancel = True
'Reminds the user to click Exit button to close.
Msg = "Click Exit to close."
Ans = MsgBox(Msg, vbInformation, "Vocational Services Database - " & ActiveSheet.Name)
Select Case Ans
Case vbYes
End Select
End Sub

This portion of the code is located in Module 1:

Option Explicit

Sub Exit_Referrals()
Dim Msg As String, Ans As Variant
Msg = "Would you like to Exit the Referral Workbook?"
Ans = MsgBox("Would you like to Exit the Referral Workbook?" & vbCr, vbYesNo, "Vocational Services Database - " & ActiveSheet.Name)
Select Case Ans
ThisWorkbook.fMacro = True
Case vbYes
Sheets("TOC").Select
Application.Calculation = xlCalculationAutomatic
ThisWorkbook.Save
Application.Quit
End Select
End Sub

If I use the active X Button (the way I want to close) a error message arrears:
Compile error: "Statements and labels invalid between Select Case and first Case" What can you suggest that I look at?

I tried changing ThisWorkbook.fMacro = True to If Not fMacro Then cancel = False

The message I get now is Variable not defined. Any suggestions?
  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Using the red X (Upper right hand corner) for another purpose.

I don't know if that link worked, so here is the current code:
This is located in ThisWorkbook & does work.


Public fMacro As Boolean
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
Dim Msg As String, Ans As Variant
'Cancels The Red X on both the Application & the "Click....."
window.
If Not fMacro Then cancel = True
'Reminds the user to click Exit button to close.
Msg = "Click Exit to close."
Ans = MsgBox(Msg, vbInformation, "Vocational Services
Database - " & ActiveSheet.Name)
Select Case Ans
Case vbYes
End Select
End Sub

This portion of the code is located in Module 1:

Option Explicit

Sub Exit_Referrals()
Dim Msg As String, Ans As Variant
Msg = "Would you like to Exit the Referral Workbook?"
Ans = MsgBox("Would you like to Exit the Referral Workbook?" & vbCr,
vbYesNo, "Vocational Services Database - " & ActiveSheet.Name)
Select Case Ans
ThisWorkbook.fMacro = True
Case vbYes
Sheets("TOC").Select
Application.Calculation = xlCalculationAutomatic
ThisWorkbook.Save
Application.Quit
End Select
End Sub

If I use the active X Button (the way I want to close) a error message
arrears:
Compile error: "Statements and labels invalid between Select Case and
first Case" What can you suggest that I look at?

I tried changing ThisWorkbook.fMacro = True to If Not
fMacro Then cancel = False

The message I get now is Variable not defined. Any suggestions?


Start with putting your global var in Module1 to make it public because
ThisWorkbook is a private class.

I'd use an entirely different approach, though, depending on what task this
project is required to do. I'm willing to review your project (if you wish) to
make suggestions and/or pass back sample/example approaches. This requires I
have a working copy of the workbook with a reasonable number of sheets to get
some idea of its magnitude!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #18   Report Post  
Junior Member
 
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

Thanks for explaining about my global var. I did indeed move it to Module 1. I assume that it should be placed right below "Option Explicit".
The workbook has 68 sheets. Some of the sheets have (Dropdowns) that refer to 3 others. 5 sheets are used for reports. 1 is a table of contents, the other sheets are basic data entry. This workbook is one where my supervisor asked me to combine multiple workbooks into one. She has used these workbooks for several years, I am kind of stuck with the layout.

What I don't understand is, it looking for when I use "ThisWorkbook.fMacro = True"

I am not familiar with this site, can you upload sample, in a PM?

Last edited by FrancisM : June 28th 19 at 06:31 PM
  #19   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Using the red X (Upper right hand corner) for another purpose.

Thanks for explaining about my global var. I did indeed move it to
Module 1. I assume that it should be placed right below "Option
Explicit".
The workbook has 68 sheets. Some of the sheets have (Dropdowns)
that refer to 3 others. 5 sheets are used for reports. 1 is a table of
contents, the other sheets are basic data entry. This workbook is one
where my supervisor asked me to combine multiple workbooks into one.
She has used these workbooks for several years, I am kind of stuck with
the layout.

What I don't understand is, it looking for when I use
"ThisWorkbook.fMacro = True"

I am not familiar with this site, can you upload sample, in a PM?


I'll rewrite the code you posted and you can try that...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #20   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Using the red X (Upper right hand corner) for another purpose.

Too much missing code but here's your Exit_Referrals routine revised...

Option Explicit

Public bMacroRun As Boolean


Sub Exit_Referrals()
Const sMsg$ = "Would you like to Exit the Referral Workbook?"
Const sTitle$ = "Vocational Services Database - " _
& Sheets("TOC").Name
If MsgBox(sMsg, vbYesNo, sTitle) = vbYes Then
bMacroRun = True
With ThisWorkbook
Sheets("TOC").Calculate: .Save
End With 'ThisWorkbook
Application.Quit
End If 'bMacroRun
End Sub

If you make a copy of the workbook with some (perhaps unnecessary) pages
removed I can download and take a look...

--
Garry

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


  #21   Report Post  
Junior Member
 
Posts: 14
Default

Does this site allow private messages? I could not find anything.
  #22   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Using the red X (Upper right hand corner) for another purpose.

Does this site allow private messages? I could not find anything.

No, nothing is available on this server for private messages!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #23   Report Post  
Junior Member
 
Posts: 14
Default Using the red X (Upper right hand corner) for another purpose.

GS[_6_] I really appreciate, you taking time out of your busy schedule to assist me. I look forward to seeing your code, & what I will learn.
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 09: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 08:07 PM


All times are GMT +1. The time now is 01:50 PM.

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

About Us

"It's about Microsoft Excel"