Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110981 > unrolled thread

Using the red X (Upper right hand corner) for another purpose.

Back to article view | Back to microsoft.public.excel.programming

Contents

  Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.1752ea28@excelbanter.com> - 2019-06-21 19:57 +0100
    Re: Using  the red X (Upper right hand corner) for another purpose. GS <gs@v.invalid> - 2019-06-22 22:28 -0400
      Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.17566e28@excelbanter.com> - 2019-06-24 12:48 +0100
        Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.17567c38@excelbanter.com> - 2019-06-24 13:20 +0100
          Re: Using  the red X (Upper right hand corner) for another purpose. GS <gs@v.invalid> - 2019-06-24 13:52 -0400
            Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.1756dea8@excelbanter.com> - 2019-06-24 20:08 +0100
              Re: Using  the red X (Upper right hand corner) for another purpose. GS <gs@v.invalid> - 2019-06-24 16:16 -0400
                Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.1757bfa8@excelbanter.com> - 2019-06-25 12:23 +0100
                  Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.1757f7e8@excelbanter.com> - 2019-06-25 15:54 +0100
                    Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.17596589@excelbanter.com> - 2019-06-26 18:32 +0100
                      Re: Using  the red X (Upper right hand corner) for another purpose. GS <gs@v.invalid> - 2019-06-26 16:36 -0400
                      Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.175a62a8@excelbanter.com> - 2019-06-27 12:07 +0100
                        Re: Using  the red X (Upper right hand corner) for another purpose. GS <gs@v.invalid> - 2019-06-27 12:37 -0400
                      Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.175ab708@excelbanter.com> - 2019-06-27 17:59 +0100
                        Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.175bb428@excelbanter.com> - 2019-06-28 12:32 +0100
                          Re: Using  the red X (Upper right hand corner) for another purpose. GS <gs@v.invalid> - 2019-06-28 12:16 -0400
                            Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.175c0888@excelbanter.com> - 2019-06-28 18:28 +0100
                              Re: Using  the red X (Upper right hand corner) for another purpose. GS <gs@v.invalid> - 2019-06-28 15:00 -0400
                                Re: Using  the red X (Upper right hand corner) for another purpose. GS <gs@v.invalid> - 2019-06-28 15:30 -0400
                                  Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.175fa8a8@excelbanter.com> - 2019-07-01 12:07 +0100
                                    Re: Using  the red X (Upper right hand corner) for another purpose. GS <gs@v.invalid> - 2019-07-01 08:09 -0400
                                      Re: Using  the red X (Upper right hand corner) for another purpose. FrancisM <FrancisM.17614078@excelbanter.com> - 2019-07-02 17:26 +0100

Page 1 of 2  [1] 2  Next page →

#110981 — 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.




-- 
FrancisM

[toc] | [next] | [standalone]

#110982

> 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

[toc] | [prev] | [next] | [standalone]

#110983

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




-- 
FrancisM

[toc] | [prev] | [next] | [standalone]

#110985

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.




-- 
FrancisM

[toc] | [prev] | [next] | [standalone]

#110986

> 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

[toc] | [prev] | [next] | [standalone]

#110987

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.




-- 
FrancisM

[toc] | [prev] | [next] | [standalone]

#110988

> 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

[toc] | [prev] | [next] | [standalone]

#110989

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




-- 
FrancisM

[toc] | [prev] | [next] | [standalone]

#110990

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.




-- 
FrancisM

[toc] | [prev] | [next] | [standalone]

#110991

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.




-- 
FrancisM

[toc] | [prev] | [next] | [standalone]

#110992

> 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

[toc] | [prev] | [next] | [standalone]

#110993

Here is a link.


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




-- 
FrancisM

[toc] | [prev] | [next] | [standalone]

#110994

> Here is a link.
>
>
> https://1drv.ms/x/s!Ak-4iXjPpsJMgSUd6aA7-RVt0LS1?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

[toc] | [prev] | [next] | [standalone]

#110995

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.




-- 
FrancisM

[toc] | [prev] | [next] | [standalone]

#110996

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?




-- 
FrancisM

[toc] | [prev] | [next] | [standalone]

#110997

> 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

[toc] | [prev] | [next] | [standalone]

#110998

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?




-- 
FrancisM

[toc] | [prev] | [next] | [standalone]

#110999

> 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

[toc] | [prev] | [next] | [standalone]

#111000

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

[toc] | [prev] | [next] | [standalone]

#111001

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




-- 
FrancisM

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | microsoft.public.excel.programming

csiph-web