How do I use the same BeforeClose event on multiple workbooks

Originally I asked how to prevent users from closing a 
workook using the close button.  The answer was to put 
some code in the workbook's BeforeClose event.  

If a new workbook is opened during program execution, how 
do I add this code to that workbook's BeforeClose event 
without going into VB and adding it?  Can that be done 
through code?

~Jenny
0
anonymous (74722)
8/19/2004 5:50:46 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
109 Views

Similar Articles

[PageSpeed] 48

Jenny,

Here is some code to create a BeforeClose event in the active workbook.

You will need to work out how to invoke it, and add the real code, not just
my Msgbox

    With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
        .InsertLines .CreateEventProc("BeforeClose", "Workbook") + 1, _
            vbTab & "If Range(""A1"").Value > 0 Then " & vbCrLf & _
            vbTab & vbTab & "Msgbox ""Hi""" & vbCrLf & _
            vbTab & "End If"

    End With


-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jenny" <anonymous@discussions.microsoft.com> wrote in message
news:970601c48615$0e5bd0f0$a601280a@phx.gbl...
> Originally I asked how to prevent users from closing a
> workook using the close button.  The answer was to put
> some code in the workbook's BeforeClose event.
>
> If a new workbook is opened during program execution, how
> do I add this code to that workbook's BeforeClose event
> without going into VB and adding it?  Can that be done
> through code?
>
> ~Jenny


0
bob.phillips1 (6510)
8/19/2004 6:05:15 PM
Reply:

Similar Artilces:

Conditional Comment using Cell value as part of it
What I'm trying to do is the following: if cell(a1) is different from 0(zero) then, cell(b1) should have a comment that say, the value of cell(c1) is the payment, next line the value of cell(a1) was refund, next line Total for today = cell(c1)-cell(a1) Example a1 = $10.00 c1 = $30.00 B1 (comment): $30.00 is the payment (c1) $10.00 was refund (a1) Total for today: $20.00 (c1-a1) can anyone help me on that? thanks in advance ...

Why is .NET 2.0 required to deploy an app using VC2005 C++ Setup project?
I just created a setup project for my mfc app. I noticed that in the prerequisites section the .NET 2.0 is checked by default. My app does not need the framework (at least this is what I think). If I clear the checkbox, build the project, and then try to deploy it on a machine with ..NET 1.1 the installer fails with a message complaining about missing .NET 2.0. I'm not sure if this the installer that needs the framework or my app. Are there any mfc8 dlls dependant on the framework? Thanks Hi, > I just created a setup project for my mfc app. I noticed that in the > p...

Outlook 2003 opens multiple instances
When I double click the desktop icon for outlook it opens twice or has two instances available in the task manager window. Once I disable or end these and reopen outlook it is fine. Also, we are currently running Viewmail and Viewfax from Active Voice. Any help would be grealty appreciated. Charles Garcia wrote: > When I double click the desktop icon for outlook it opens > twice or has two instances available in the task manager > window. Once I disable or end these and reopen outlook it > is fine. This happens randomly for me too. In fact, for a long time it used to a...

Multiple workbooks open -- closing workbooks
Dear Clever People, I hope this is not the "silly question of the day". We were wondering (when you have multiple workbooks open and you close one workbook) why are all other workbooks forced to be closed, too? Is it possible to ever close one workbook and leave all the others open? Thank you, Janet Close it by going to file>close or by pressing Ctrl + F4 or by clicking the smaller X below the big red X -- Regards, Peo Sjoblom "Janet Panighetti" <JanetPanighetti@discussions.microsoft.com> wrote in message news:1C9BC30B-BE1A-4605-B215-99C07CB1D366...

Extended Pricing price groups should allow multiple Units of Meas.
We categorize our items without regard to units of measure, and want to make an overall change to a particular category of items, e.g. increase the discount percent for all items in that category. But we cannot group these items into one price group because they have different units of measure. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft ...

Installing CRM3, multiple errors
I have installed CRM3 on a clean SBS2003 machine (under VirtualPC), but have got a series of errors and cannot now acess CRM at all. I followed the steps in the Implmentation Guide. I had to change the web site port to be 80 and the alias to be able to access it (as crm3demo). I installed CRM3 under Administrator login and was initially able to open CRM correctly, login as Administrator, go into the Administrator user and uncheck the restricted access check box. Everything appeared normal. Then I set up the sample data users in Active Directory and tried installing the sample data by ...

Varying colors on a line chart with multiple series
In Excel 2003, is it possible to change the color of part of a line on a chart with multiple data series? I can do it on a single series chart. Hi, Yes, you would need to select the segment of the line and then format it. So select the series pause and then select the segment apply formatting Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "LKP" <LKP@discussions.microsoft.com> wrote in message news:CB5CF5B8-5E3C-4288-A7B1-63958DB0C688@microsoft.com... > In Excel 2003, is it possible to change the color of part of a line on a > chart with ...

How to use Find (Ctrl-F) to find non-alpha
Is there some way to search a sheet for anything BUT a-z or A-Z? tx ;-) Finding numerals or a mixture of numbers and letters (and other characters) works ok for me. What are you having trouble with? On 10/22/2010 16:31, Heather Mills wrote: > Is there some way to search a sheet for anything BUT a-z or A-Z? > > tx ;-) -- Dave Peterson On Fri, 22 Oct 2010 17:54:28 -0500, Dave Peterson <petersod@XSPAMverizon.net> wrote: >Finding numerals or a mixture of numbers and letters (and other characters) >works ok for me. > >What are you having trouble with? > >...

Query from Multiple Workbooks
Hello, I have 12 files in a directory with a sheet name Sales containing Sales data for each month having more than half a million rows in each workbook sheets The field names are all the same in all the files like date, item, itemgroup, branch, unit, salesman, amount etc I am looking for a way to get a query from all these workbook and get a summarised report based on criteria like sales by salesman, by month, by product, by product group, by date, by branch etc. Is this will be possible through code?. Itry to use MS query but not successful Using Excel 2007 and Win XP ...

VC++ 2005
I am attempting to compile a project in Visual Studio 2005 which compiles and links properly in VC++ 6.0. It seems to have problems with C++ i/o functions. Here is the error I get: g:\src\analyzer\digitdialpages.h(136) : error C2146: syntax error : missing ';' before identifier 'm_InFile' Line 136: ifstream m_InFile; The statment: include <fstream> is at the top of the source file. Some assistance would be helpful. We didn't have any problems with using ifstream or ofstream in VC++ 6.0. Thanks. On Tue, 12 Dec 2006 18:22:47 -0500, "Raj Kulkarni...

Excel 2000 Shared Workbook Missing Worksheets
I am experiencing a problem in an Excel 2000 shared workbook. Worksheets are continously missing from the shared workbook. Users are connecting to the shared workbook using a Citrix connection. They enter their data into the worksheet and then save the workbook. The shared workbook is password protected so that users can not get exclusive rights and delete or move sheets. I've also tried tracing the changes and looking at the history to find the problem. Any help or advice would be greatly appreciated. --- Message posted from http://www.ExcelForum.com/ ...

How do I create custom border using peoples names?
There are many it could be done. I'd set up text boxes and go from there. -- JoAnn Paules MVP Microsoft [Publisher] "Val" <Val@discussions.microsoft.com> wrote in message news:BFF16BB3-4C90-4E05-B0AA-303874A445AA@microsoft.com... > In addition to JoAnn's reply, you could use WordArt as well. WordArt allows you the opportunity to rotate, flip, arrange etc. and resize as well as save as an image. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. &...

OL2003 Comining Multiple Contacts
As I mentioned in another post, I'm playing around with Outlook. Here's my Contacts problem. I was using Outlook at work and synchronizing my Contacts with my Palm Pilot. So that part of the transition was a breeze. But I was using a completely different email program at work which has it's own address book. I know how to import that address book into Outlook, but the end result will include be a whole bunch of partial duplicate entries where say, I have name and address information in the old Palm/Outlook database but only an email address in the email address book. Any sugge...

Exchange 2003 And Multiple Managers
We need to deploy our organisation to allow for some users to have multiple Managers. For example, a secretary can report to a number of different Managers depending on what work they are performing at the time. Any help on how this would be achieved would be greatly appreciated. "Davie" <dxw@mms.co.uk> wrote: >We need to deploy our organisation to allow for some users to have multiple >Managers. For example, a secretary can report to a number of different >Managers depending on what work they are performing at the time. > >Any help on how this would ...

opening multiple blank documents on start-up
Hi, I'm trying to figure out how I can open multiple blank documents on my (MDI) application's startup. (using VC++ 6.0) I've looked at the commandline info stuff, but it looks like I can only tell it to do one FileNew. Basically I just want, say, n new documents created (along with their views, presumably) when I start my program. Anyone have any ideas on how I might do this, or can suggest where I should look for answers? thanks! Brian In principle: make the doc template a member object in your app.h CMultiDocTemplate* m_pMyDocTemplate; use this pointer in the .cpp temp...

worksheet_calculate event changes worksheets, but shouldn't
Hi, The code below is in the Sheet1 (Budget) Module: The user is entering data into the "Budget" worksheet. When this fires, it should hide some rows on the Categories worksheet. It does work ok, however, everytime a user enters a new figure on the Budget worksheet, it fires and momentarily flashes over to the Categories worksheet. It is quite distractive, especially when one has hundreds of figures to enter. I set a trap to stop it on the top line of this code - the Private Sub Worksheet_Calculate() line. Then, when I enter a new number on the Budget page, it fi...

Printing multiple emails in outlook 2002
Just wondering if it is possible to select multiple emails in outlook and have them print on the same page. Thx Figured it out. Select multiple emails, go to file>print and make sure "Start each email on a new page" is deselected. >-----Original Message----- >Just wondering if it is possible to select multiple emails >in outlook and have them print on the same page. > >Thx >. > ...

how to remove non commercial use in the title bar in office
how to remove non commercial use in the title bar in office 2007 Buy an edition that is not for non-commercial use only. You own the Home and Student edition. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Mario" <Mario@discussions.microsoft.com> wrote in message news:0E14F850-0B65-4093-926F-B680E1C7D6B1@microsoft.com... > how to remove non commercial use in the title bar in office 2007 ...

vlookup cant be used
I need a way to transfer data. I have one sheet that contains all information. All of this data needs to be transferred to a template ready for print. I have contemplated using a vlookup to quicken the method, although the data sheet will be moving too and from computers, and so the vlookup will not work. Is there any code that might possibly help me, i.e. a code that looks for the datasheet and does some sort of vlookup after it has located the file? Or should this problem be tackled using macros, although i imagine that this will have the same effect. How "ready for print"? Are...

How to count/summarize multiple Criteria
hello, I am a very basic excel user and I need help to create a formula that will count multiple criteria. For example: I have a worksheet with my data and another to summarize my data. Each row in my data sheet, "Corproate Projects List",tracks the detail of a specific project assigned to my department. Column A tracks whether each individual project is currently open, closed or ongoing. Column D tracks the department requesting the project. The rest of the sheet doesn't matter with respect to my question on how to write the formula...... In my summary sheet I want to count th...

Linked Workbooks Question
Hi All, I have a workbook that links to two other workbooks. Two carry data, one does the calcs... All three workbooks are in the same directory. When I open the 'calcs' workbook, I allow macros then update links. References to cells in the data workbooks show as #value errors. If I have all three open, the 'calcs' sheet works fine. What gives ??? Thanks to the group. ...

Upgrading Multiple Companies at Different Times
I have multiple companies currently in my GP 8.2 installation on a single server. Of course these companies each share the DYNAMICS database. I am plotting out the upgrade process to 9.0 and would like to upgrade one company at a time, as one of them is ready to go, while the other will require more work due to customizations. Would there be any issue with upgrading CompanyA to 9.0 on a new server, and then a month or two down the line, upgrading CompanyB to that same server? Would CompanyB be able to slide right in to using a DYNAMICS database that it had not used for a couple of...

Event trigger in Excel?
I have 2 columns. Column 1 is text. Column 2 says: =IF(A1="","",Today()). So if column 1 has data, column 2 gets today's date. Question: Is there a way to have column 2 update itself based on a change to column 1. My preference is that column 2 sets itself to the date an update was made, and then changes ONLY when another update is made. I can do it easy enough in Access with an event trigger and some simple VB, but not sure if the same can be accomplished. If there is a way to do this, can someone point me in the right direction? Thanks Tim http://ww...

upgraded Excel 97 to 2003, one of our older workbooks wont print .
We have a large number of Excel 2000 worksheets (many generated by macros) we have had no problems except for one sheet in one book that will not print. We print preview and see in this case 3 pages. Printer receives nothing. Our printer is a Xerox Phaser 3115. Only way we can get it to print is Move/Copy to new workbook which then prints OK Any ideas would be appreciated ...

Problem with "Send as", when using "send on behalf of" in Exchange 2000.
I'm not able to set the "Send as" permission on a mailbox. The problem is that the access right is not present in "Mailbox Rights" ?. I have 2 users, A and B. All mail to B is forwarded to A, and B has added A to "Send on behalf of" in Active directory users and computers / Exchange general / Delivery options. When not being able to set the "Send as" creates the following problem: If A reply to a mail originally send to B (which is automaticaly forwarded to A) , the receiver sees A as the sender. Any idears. Thanks in advance Bo Jacob...