Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old June 26th 19, 09:36 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,042
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  
Old June 27th 19, 12:07 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2019
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  
Old June 27th 19, 05:37 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,042
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  
Old June 27th 19, 05:59 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 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  
Old June 27th 19, 06:22 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2019
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  
Old June 28th 19, 12: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.

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  
Old June 28th 19, 05:16 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,042
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  
Old June 28th 19, 06:28 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2019
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  
Old June 28th 19, 08:00 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,042
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  
Old June 28th 19, 08:30 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,042
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


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 11:51 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