Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
|||
|
|||
Using the red X (Upper right hand corner) for another purpose.
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
|||
|
|||
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.
|
#16
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
|||
|
|||
Does this site allow private messages? I could not find anything.
|
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink to cell and have it appear at top upper left hand corner | New Users to Excel | |||
Moving Active Cell to Upper Left Hand Corner of Screen | Excel Programming | |||
The 'X' in the upper right hand corner is suddenly dithered? Help | Excel Discussion (Misc queries) | |||
What does a small green triangle in the upper left hand corner of. | Excel Discussion (Misc queries) |