Macro Runs On Wrong Workbook On Close

Jul 29, 2009

I have a macro on a particular workbook that runs when the workbook closes.

This runs fine when I close the workbook itself. If I happen to have other workbooks open and then I close EXCEL rather than an individual workbook, my macro that runs on close always runs when it is not the active workbook.

To put it another way, say that my 'run on close' macro is workbook 1. I also have workbooks 2 and 3 open as well. If workbook 3 is the active workbook and I choose to close Excel (and thus close all three workbooks), my 'run on close' macro runs straight away and, worse still, will run it on workbook 3 (my active workbook).

How can I stop the macro running until it is the active workbook in the closing chain, so that it only runs on the correct workbook? (workbook 1 in my example)

View 9 Replies


ADVERTISEMENT

Excel 2003 :: Ctrl-e Runs Wrong Same Name Macro In Different Workbook

Jun 17, 2012

I have a group of Excel (2003) template files, each with a different name. When these files are opened they ask for certain information and save themselves as a unique name. Within these files are VBA routines and functions and, because these files basically do the same things, they have the same name. These routines exist in their own unique modules each with a different name. Unfortunately, in these files there are differences in the number of columns of information these routines act on so once they have been saved I need them to only act on their own columns only.

My problem is, when I have more than 1 of these files open in the same instance of Excel, I try to run a file using a control letter combination Ctrl-e and it sometimes runs the version of the macro in the wrong file. This leads to all kinds of strange thing happening. What I can do to force the correct macro to work in all instances?

View 2 Replies View Related

Open Workbook Macro Only Runs Once..

Jun 30, 2009

i have a button on my workbook1 that opens another workbook (lets call it workbook2) and automatically runs a macro on opening it. Once opened i have a button on workbook2 to close itself. I then go back to see workbook1. This all works but then when i click on the button to open the workbook2 again it opens but the macro does not run.

View 4 Replies View Related

Macro Is Closing Wrong Workbook Before Going To The Next Workbook

Aug 12, 2009

I run most of my macros out of a file called "Personal.XLS" and I have this bit of code at the end of this particular macro which is closing personal.xls instead of the active file it is working on. It is set up on a loop and it should go to the next file in the directory but since it closes the file I run the macro from, the macro stops immediately. It appears that it believes "ThisWorkbook.Close" is referring to the file from which the macro is initiated.

View 2 Replies View Related

Code To UNPROTECT Then PROTECT Workbook When Macro Runs

Jul 24, 2014

Okay, I have this code and it works very well - but it only works if my WORKBOOK is unprotected. I know how to Unprotect then Protect an ActiveSheet, but I can't figure out how to Unprotect then Protect my workbook when the macro runs..

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ActiveWindow.SelectedSheets

[Code] ......

View 9 Replies View Related

Accidently Executing A Macro On The Wrong Sheet Within A Workbook

Sep 3, 2009

When running a macro is there a way you can execute code on another sheet within a workbook without being in the original sheet?

I seem to have a problem if I accidentally run a macro not on the original sheet whereby the code is executed and fills over data not in the desired sheet and the previous cells information is lost and the only way to fix the problem is to close the workbook and re-open a saved copy as you can't undo?

View 9 Replies View Related

Macro Addin That Inserts / Runs And Removes Module In Active Workbook

Mar 24, 2013

I have a macro that copies data from several workbooks saved in a particular folder to a master workbook.

The problem is that this macro only works when it is included as a module in the master workbook. Unfortunately, I cannot ask the users of this macro to insert a module, copy the code and run it each time they want to consolidate the master workbook. Therefore, I wanted to use this code by including it in a Add-In that I already prepared. The problem is that this consolidation macro does not run when it is in an add-in. Create a code that I can include in a macro add in that would insert a module in the master file, include the code that I already have, run it and then delete the module? The add-in would be password protected.

View 7 Replies View Related

Close And Reopen Workbook From Macro Stored Inside Workbook

Mar 30, 2007

I have a Macro that is stored in a specific workbook,"Projection Modeler.xls" the last line performed in this macro is the close workbooks without saving chages(this needs to be done due to the way this macro was written, and it would take too long to rewrite). If I would then like to reopen this workbook, and then rerun this macro, can I do this from inside the current macro (in a new sub routine, for instance), or do I have to write a macro outside of the workbook and then open the workbook and call the routine?

View 4 Replies View Related

Close Workbook With Macro

Sep 22, 2009

I have a workbook that contains 4 macros. This workbook is copied by vb.net application with another name. When I run the macro in the copied worksheet, the Original file/workbook is opened automatically, and it is open until the copied workbook is closed.

My question:

1) Is this normal, when a copied workbook invokes a macro from the original workbook, the original workbook opens?

2) Is it possible to close the original workbook while executing the macro from the copied workbook?

3) Will that affect the macro in the copied workbook?

4) If possible to close the original workbook, kindly help me with the code.

View 14 Replies View Related

Trigger A Macro On Workbook Close?

Dec 5, 2008

When I open my workbook it also opens a separate workbook and hides second book.
when I close myworkbook I want to unhide the hidden one and close it without saving.

View 4 Replies View Related

Crashes When Using Macro To Close Workbook

Dec 19, 2009

I have the following strange problem. A simple macro 'ActiveWorkbook.Close' causes excel to crash if I use it with a button on my sheet (a form). When I run the macro via developer > macros > run
the macro runs as expected. Even if a run the macro with alerts (thanks to jproffer) the problem persist.

View 8 Replies View Related

Macro To Close Workbook Without Saving

Jan 9, 2007

i m designing a test for a school and want to add a macro that will be assigned to a button at the end of the test i want the macro to close down the the excel program running and not prompt the user to save the workbook is there a vb code that can do this i have tryed a few but they all come back object not found.

View 6 Replies View Related

Macro To Open / Close / Save Workbook?

Mar 14, 2014

I need to have a workbook (all of them in a folder, ideally) refresh in the middle of the night. The file is large so the refresh takes a full minute. I know I can't auto refresh a closed workbook (pretty sure anyway), and I can't leave the workbook open for various reasons.

So I'm thinking I could have a macro in an open excel (it kicks off on open) that will open a particular workbook elsewhere (the one I need refreshed), open it, refresh it and close/save it. Ideally it would do this for the entire group of workbooks in the folder but I'd be happy to start with just one. I would like to set it to occur every twelve hours (so at midnight, say, this would occur - I would just have to leave the workbook with the macro in it open on a computer so that it runs and completes this task every night).

View 7 Replies View Related

Script To Run Macro On Workbook Open And Close

Nov 13, 2011

I am relatively new to writing script in excel. I have a 34 sheet workbook that includes inputs, intermediate calculations and then reporting outputs.

I am trying to write macros to do the following

1. On opening - all sheets except a contents page (with macro buttons on it) are hidden
2. One of the macros on this page will unhide all sheets (i.e. a 'developer mode')
3. One of the macros will unhide all relevant input and reporting sheets (i.e. a 'user mode')

I originally set up macros to run on workbook opening that hid all sheets except the contents page and this was working but ran into trouble when I opened up the 'User Mode' saved my changes and exited - the macro didnt work when I went to open again (I think because some of the sheets were visible and some werent and it was trying to hide sheets that were already hidden).

To counter this I set up a macro that made all worksheets visible on workbook closing - this works except I keep getting a 'Save' prompt from excel because the location of selected cells (or the sheet I am viewing) has changed from last opening (not to mention the inputs may change).

I am thinking the best way to fix this as follows:

1. Keep the opening macro the same
2. Keep the user/developer macros the same
3. On closing - develop a macro that unhides all sheets after the user has selected whether or not they have made changes to the sheet in a pop up message box - if they choose yes then they are prompted to save as and then the macro to hide all sheets runs on the new version - if they choose no then they are not prompted to save the document (i.e. next time they open they will be opening back at the contents page with the same inputs they started with in the previous session).

View 1 Replies View Related

Allow User To Restart Macro Or Close Workbook

Jun 25, 2008

I have a macro that runs when the workbook opens. At the end of my macro I've added a Yes/No box. If 'No' the workbook closes without saving. If 'Yes' I would like to restart the macro. I can close it ok, but I don't know how to restart the macro from line 1 when 'Yes' is selected. Here is what I have:

YesNo = MsgBox("Done! Would you like to run again?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
Case vbYes

'I don't know what to put here :(

Case vbNo
ActiveWorkbook.Close savechanges:=False
End Select

View 3 Replies View Related

Compile Error On Close Of Excel 2003 But Not On Close Of Workbook

Mar 22, 2012

I am using Excel 2003.

It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.

These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.

I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.

These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.

Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.

View 9 Replies View Related

Excel 2007 :: VBA To Save And Close All Open Workbooks Except Macro Workbook?

Mar 8, 2013

I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.

View 8 Replies View Related

Function Runs Within Workbook But Not As AddIn

Feb 6, 2014

When running the following code for NSLookup in a module within the current workbook, it runs fine but when I put it in a XLAM file and try and use it as an addin I only get #NAME? as a result. I believe that that addin is installed properly because I have a second module with a sub routine in it (GetIPStatus) that is working, and when entering the command =NSLookup(A2) in a cell it shows up properly like it's an available function, but it does not seem to execute. Do I have to do anything special for a function to run as an addin?

View 4 Replies View Related

VBA Runs If Workbook Is Open Or Closed?

Jan 28, 2014

The code below doesn't run if the workbook that is saves to is all ready open. is there a way for the macro to run if the workbook is closed or open?

[Code] ......

As with bigger files i will open the folder before i run the macro.

View 6 Replies View Related

Closing Workbook Runs BeforeClose VBA Twice

Sep 11, 2009

In the various codes I am using AutoOpen, AutoClose, Workbook_BeforeClose and Workbook_BeforeSave Subs. BeforeClose and BeforeSave are in '_ThisWorkbook'; AutoOpen and AutoClose are in a Module. I also use Workbook_Open, Workbook_Activate and Workbook_Deactivate, also in _ThisWorkbook. I put message boxes thru out code to be sure code was running right. The Workbook_BeforeClose is running twice under certain condition (when a cell in active workbook sheet is blank ("")). I do not understand why it is running twice.

For condition where there is data in cell M10, this creates a whole other issue ( multiple running of mutliple various Subs) which I believe I should deal with in different thread/question.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Worksheets("PanelSched").Activate
MsgBox "Test-Before close"
If Range("M10") <> "" Then
Dim PanelName As String
PanelName = Range("M10").Text

View 9 Replies View Related

Refresh All Queries On Workbook Open When Finished Print To PDF Then Close Workbook

Jan 23, 2014

I have data that daily needs to be refreshed and printed to pdf.

I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.

I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).

View 4 Replies View Related

Excel 2010 :: Close Second Workbook From Workbook BeforeClose?

May 27, 2014

I cannot make this work in XL2010.

I am opening a second workbook in ReadOnly in the Workbook Open and Closing it in the Workbook BeforeClose.

I don't EVER want to save changes on either workbook.

I need to leave excel open as the macro created a 3rd workbook which the user will need.

[Code]....

View 6 Replies View Related

Copy Range To New Workbook & Close Existing Workbook

Jun 30, 2008

I have a very brief question

dlgAnswer = Application.Dialogs(xlDialogOpen).Show
Selection.Copy
Windows("Derivative YK pricing Mod G.xls").Activate
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C5").Select
Application.CutCopyMode = False
Windows("EXPORT1.xls").Activate
ActiveWindow.Close

As you can see, I open a workbook with the dlgAnswer, I was wondering how I could close that opened workbook

Currently I have

Windows("EXPORT1.xls").Activate
ActiveWindow.Close

however the workbook will not always be called export1.xls, I want it to close whichever one I opened earlier.

View 9 Replies View Related

Creating A Macro, That Creates A New Button, Which Itself Runs A Macro..

Dec 15, 2009

I have a long complicated macro that processes a ton of data and gives the output in a new sheet. I then want to create new buttons (or some sort of user clickable triggers) on this new sheet that runs another macro.

I have got the actual adding of buttons in the sheet working, but I can't find a way to automatically assign macros to newly generated buttons during runtime. Is this even possible (or are there any clever tricks I could use to get around this?)

View 3 Replies View Related

Building Macro That Runs Another Macro Based On Certain Columns

Jun 6, 2012

Essentially, I am working with a database for a school and up until this point, each row matches up to a student with a bunch of information on them. One of these pieces of info is their parent's names. This is a much simplified look at things, but the screenshot below is a basic version of what everything looks like:

Now, the school wants me to split up the parents names from the student's and place them in their own row. I have recorded a macro to do this where you make the active cell the "full name" column for the row you want to expand, and then run the macro. Afterwards, up to two more rows are added, with the parent's names and some data copied. Below is a screenshot of what that looks like: (yellow is the new data generated, and blue is the active cell I marked to run the macro properly)

The issue is that there are thousands of these that need to be done, and I figured there is a way to do this with a macro. The issue is that not every row has parents names to expand out.

In layman's terms, I need a macro that will look at the two columns titled "Father's Name" and "Mother's Name" on each row of the spreadsheet and then, if there is any data in both or either of them, it should set the active cell to the "Full Name" column for that row then run the macro accordingly. After that it should repeat this all the way down, skipping any where neither of the columns have text in them

In the actual spreadsheet, the columns that need to be checked are AW and AX, and the column that needs to be set to the active cell before running the macro is B.

View 5 Replies View Related

Macro That Takes Workbooks Or Files And Runs Them Through Another Macro

Apr 25, 2007

i need to make a macro that takes workbooks or files and
runs them through another macro. i already have the 2nd macro
done and it is working perfectly i just need to know how to make the one
that finds the other files and runs them all through the macro i already made. My boss said that he will have about 150-200 files to run through this macro.

View 9 Replies View Related

Macro Only Runs Once

Nov 28, 2009

I can't seem to find an answer for this, but as an alternative, on Worksheet Activate the code runs and places a 1 in Range("A1") and therefore the next time the macro will not run.

This works fine for me, but just thinking if somebody who doesn't know why the one is in A1 and deletes it this macro will run again on sheet activate.

Private Sub Worksheet_Activate()
Dim LR As Long
Dim rng As Range

If Sheets("Charts").Range("A1").Value = 1 Then Exit Sub

LR = Sheets("POD").Cells(Rows.Count, "B").End(xlUp).Row

With Sheets("Charts")
.Range("I129").FormulaR1C1 = "=ROWS(R1C1:R[-128]C[-8])"
Set rng = .Range("A129").Resize(, 10)
rng.Copy rng.Resize(LR - 4)...............

View 9 Replies View Related

Disabling "x" To Close & Creating Macro Button To Close

Oct 11, 2006

I need to know the command to close a workbook. I have used the information found here: http://www.ozgrid.com/VBA/userform-close.htm to disable closing the workbook via the upper right "X", but I need a button that, when clicked, simply closes the workbook instead.

View 2 Replies View Related

Macro Only Runs Manually

Feb 5, 2008

Running Windows XP; Excel 2003

I have a fairly simply macro.

It copies certain sheets out of a workbook (for a new workbook)
Unprotect the sheets in the new workbook
Deletes a particular cell on each sheet
Unhides a sheet
Sets all of the new worksheets to normal view
Protects the new sheets
Lets the user save and name the new workbook.

All is working fine Except it will not change the new workbook from Page Break View to Normal view.

The code I'm using = Sheets("name").Activate
Activewindow.View=xlNormal

Here's the strangest part. When I run the macro from a command button, the newly created workbook retains the page break view (as is in the original).

When I run the macro line by line via F8, IT WORKS JUST FINE

View 9 Replies View Related

Macro Runs For A Bit And Then Crashes

Feb 15, 2008

I have created a macro in xl2000 which was working fine - although through corporate upgrades my machine has now been upgraded with xl2003 and the macro runs for a bit and then crashes on the code below, although it only highlights teh 'rnum'

rnum = ActiveCell.Row
Is there a difference between xl2000 & xl2003?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved