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

Groups > microsoft.public.excel.programming > #110997

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

Show all headers | View raw

> 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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web