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


ADVERTISEMENT

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 View Related

Closing A Workbook Without Saving - Macro

Nov 28, 2008

Need the VBA code to close a workbook, and not save it? I need it to open a workbook, run a macro, and close without saving. The code I have thus far is:

View 3 Replies View Related

Macro Commands When Closing Workbook

Dec 13, 2011

Is there a way (with macros, I''m assuming), to make my workbook always return to the Main tab and Save automatically --- every time somebody closes the workbook from any tab - so that the next person who opens the workbook will have it ready to go on the main tab?

View 5 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 To Display A Message Before Closing Workbook

Jul 10, 2009

how I would display a message to the user before the workbook is closed regardless if any changes have been made e.g.

Ideally there would on be an “OK” option only by way of acknowledgement and also no option to close the message by the ‘x’ in top right corner.

View 6 Replies View Related

Macro For Closing And Reopening A Workbook Read-only

Jul 21, 2009

I have a pair of macros that together close and reopen a workbook when the user clicks on a button labelled 'Refresh'. The reason for the macros is that any one of multiple users may have the workbook open for editing while the others have it open read-only, and those who have it open read-only will want to refresh it periodically to make sure they're viewing the most up-to-date version.

Now, here's the hitch. If I have the workbook open read-only and refresh it while no-one else has it open for editing, it doesn't reopen read-only (it reopens for editing). But I don't want it to reopen for editing (I want it to reopen read-only). Is there anything I can add to the macros to make the workbook reopen read-only, or is there a different pair of macros I can use? Here's what I have at the moment:

Macro 1
Sub CloseMe()
'
Application.OnTime Now, "OpenMe"
ThisWorkbook.Close SaveChanges:=False
'
End Sub

Macro 2
Sub OpenMe()
'
End Sub

View 9 Replies View Related

Macro: Copy Cells To Another Workbook On Closing

Oct 31, 2006

I have a worksheet template with details in cell A1,B1,C1,D1,E1,F1,G1,H1 & I1and when the sheet closes I would like to save a copy of the cell contents to a different workbook called transaction history, is there any way that vba code could write this to the workbook and when the transaction happens again, it then writes the new transaction data to the next row within the same workbook transaction history

View 6 Replies View Related

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

Closing Workbook When Closing Userform

Sep 14, 2006

I have a userform which opens when the workbook opens. Ideally Id like to have the userform open without the workbook coming up and have the workbook close when you close the userform. If thats not doable then is there a way to just close the workbook when the userform is closed?

View 3 Replies View Related

Closing A Workbook From Another Workbook

Apr 27, 2006

I have a Main Menu with 5 or 6 options of bringing up other workbooks. In the workbook(s) that I choose, I have the command Workbooks("Main_Menu.xls.close") followed by

My_option.show

What happens is the Main_Menu workbook closes but the My_option.show is skipped and I just enter into the Workbook that I selected. If I comment out (') the workbook.close command, the My_option.show works. I am not a programmer and I am not too good at explaining things, so if this is too confusing to follow, then just delete it.

View 2 Replies View Related

Closing Workbook At The End

Aug 22, 2012

I have a perfectly functioning piece of code. There is still one snag, however. In the process of running the macro, three new workbooks are opened. I would like to close all three of those workbooks at the end. I am using the following procedure for each of the sheets:

To open the worksheet

Code:
Dim wb As WorkbookMsgBox "Please select the Stakeholder File"SHFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Please select the Stakeholder Workbook")If SHFile "False" Then Set wb = Workbooks.Open(Filename:=SHFile)

To close the worksheet at the end:

Code:
If Not wb is Nothing Then wb.Close SaveChanges:=False

The only problem is that I am getting bugs when closing multiple workbooks (3). I have assigned the names wb, vb and ab to the three workbooks.

View 5 Replies View Related

Before Closing The Workbook

Apr 19, 2006

I have some VBA code which opens another workbook, copies data from the first sheet in there and pastes into the current sheet before closing the workbook it got the data from. Is there any way of suppressing any messages associated with closing the other workbook? i.e. do you want to save - or possibly automatically generating a no message to the prompts?

View 3 Replies View Related

Closing Workbook With VBA Without SAVING?

Nov 27, 2012

I'm trying to close a workbook without saving it. But everytime I get the message box popupped twice.

These are the codes that I use:

VB:
Private Sub Workbook_BeforeClose _(Cancel As Boolean)
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String

[Code].....

View 4 Replies View Related

Reminder When Closing A Workbook

Jan 11, 2007

I have 4 employees who at the end of their shifts have to email me an excel workbook. The issue is that some of them are forgetful and after placing numerous post-it notes on their monitors one particular employees just forgets.

What i want to do if possible is that when the workbook gets closed, a pop-up window shows up on their screen reminding them to send the workbook. Even better would be if their was a button that they could click that would email the workbook as an attachment in the pop-up box.

View 11 Replies View Related

Closing A Workbook Created?

Mar 25, 2014

I've created a macro which copies the current tab to a new workbook (Book X):

[Code]....

I then manipulate the copied data in Book X. Once this is done I move the manipulated sheet back into original file

[Code] .......

The problem i'm having is I'd like to close the newly created Workbook X file once the macro has run but Workbook X is usually called Book2, Book4, Book7, Book32 etc.

Workbook X will always be the previous file if i use the ALT TAB shortcut.

View 2 Replies View Related

Pop-up Message Appear When Closing Workbook

Dec 6, 2007

Is it possible to pop up a message when closing the workbook?

if so, could the message only popup if a certain cell is negative?

For example, This is what i would like to happen.

When workbook close is activated:
If cell A1=(-20), then pop-up "Please check your work and correct".

else, close workbook

View 14 Replies View Related

Closing Workbook & Excel

Jun 26, 2009

How do I SAVE and CLOSE Workbook and it's instance of Excel.

I have other instances of excel open that I do not want closed.

I thought that I had this before, but I can't find it if I did. I do keep a list of all the requests where I ask for help and it's not in this list.

View 6 Replies View Related

From Userform / Other Workbook Not Closing

Nov 26, 2013

I wrote this macro where, from a userform, I click a button and it will fillout another form with certain data from the original WB to a different WB.

All is working fine and exactly the way I want it except, it won't close and save the other sheet. It will go back to the userform, as I want it to, but it will leave the other file in the background open.

This is the code:

Code:
Private Sub CommandButton6_Click()
'Allocation file fill out
Dim alloc As String
Dim allwb As Workbook
Dim ABA As Workbook
Dim Prompt As String
'Dim fd As FileDialog
Set ABA = ThisWorkbook

[code]....

View 2 Replies View Related

Closing A Workbook That Is Open Via VBA

Jul 2, 2008

Well I seem to be having an issue in a form i created. What happens is that when the user clicks a button it saves a worksheet of workbook as its own file, problem is that it will actually open the new worksheet as a workbook when I really just want it to save without opening a new instance. Here is the code

Dim IntWirefilename As String
Worksheets("Intwires").Select
Worksheets("Intwires").Copy
IntWirefilename = "International Wires Database"
Worksheets("Intwires").SaveAs FileName:="File Path" & IntWirefilename & (".xls")
Workbooks("File PathInternational Wires Database.xls").Close
I think the problem lies in the last line or it could be from the select and copy part.

I do not want it to open a new file after it saves it. Also can anyone help create a part that will bypass the overwrite existing file thing so that this file will just continuously save as the same file name rather than prompting the user constantly to Overwrite the file.

View 9 Replies View Related

Prevent Closing Of Workbook By Top Right X

Nov 20, 2007

I need to alter the Close command on the title bar (the 'X' on the top right corner on the blue bar of excel)

Is there any way to alter the close button on the title bar to have it call a sub I created instead of actually closing excel?

View 6 Replies View Related

Closing The Workbook & Saving Automatically

Jan 25, 2008

I have my Excel workbook open & have made changes. Now I close it and Excel prompts me if I want to save the changes. Is there a way to just close the workbook and automatically accept any changes without have the "Do you want to save changes" message appear?

What about if it is opened in read-only mode? Typically the workbook will not save any changes unless you save it off as another file name. If there is a solution to my question above, can it be enabled only when the workbook is opened in edit mode, or will it not auto-save changes over the file because Excel knows it was opened in read-only mode?

View 9 Replies View Related

Prevent Closing Of One Workbook When Two Are Open

Sep 23, 2009

I'm trying to prevent a user from closing a perticular workbook.

Here is some important factors.

Workbook(A) = "LA-01-04-10" This is only one of 52 other but only one is open at a time.

Workbook(B) = "LA.xls" Always the same name and is always opened when one of the workbooks(A) above it opened.


When workbook (A) is opened it will then open another workbook (B) at that time the window is hidden workbook(B) So the user can only see workbook (A) I have a button on workbook(A) that unhides the window to show workbook(B) After the user completes his work in Workbook (B) they need to click another button that sorts the list and returns them to workbook(A) (Which also hides the window for workbook(B).

The problem is I have users that are trying to close workbook(B) instead of pressing the sort list button. So what I want is to prevent the user from closing workbook(B) by displaying a msgbox saying they need to press the button.

Ive tried several approches to this but I'm still having problems. The below script works if workbook(B) is active or shown. The message is displayed and the workbook will not close. However if the user is in workbook (A) and trys to close the message is displayed and both workbooks close like they are suppose to. But why is the message displayed?

One other note I should say is that not only does workbook(A)Open workbook (B) but it also closes it. Which may be why I'm having problems?

I have also included both workbooks so you can see all the code.

I have this code in Thisworkbook of workbook(B)

View 10 Replies View Related

Before Close Not Working On Closing Workbook

Sep 29, 2009

I am hoping someone can help. I am new to writing macros and have the following in the this workbook page. When opening the macro does exactly what I want and expect, but does not do the "before close" part. If I select the macro in editor and run it, it does just what I want, so dont understand what is going wrong. Do I have to put in some kind of prompt, or manually run this before close - preferably I would like it to just run.

secondly, I have password protected the workbook, but have to put the password in the macro, is there any way of making this invisible to anyone who then reads the macro....

View 9 Replies View Related

Workbook Automatically Opens After Closing

Jan 6, 2010

I have a workbook on a share drive that is used by multiple people. In the event that someone leaves the workbook open after using it, I have a timer function that pops up a splash screen after 4 minutes of inactivity which states, "This workbook will close in 1 minute if there is no further activity". If there is no further activity in that minute, the workbook closes.

The code works fine...unless the workbook is manually closed in that minute between the splash screen and when the timer would have closed the workbook. If that happens, the workbook closes normally and then briefly reopens and closes a minute later when the timer would have closed the workbook. Is there any code to prevent this?

View 4 Replies View Related

Script Works But Not After Closing Out Of The Workbook

Feb 26, 2009

I have a workbook setup with 4 sheets that I'm using. I need the 4 sheets to type in all caps when I enter the data. I found a script, and followed the directions. It works great, yet when I close out of the workbook, and open it back up, the script doesn't work anymore. I go to check the 'view code', and it still there. I resave it, and try it again, and it still doesn't work, what am I missing? This is all new to me. I am saving it as a Workbook Macro enabled file type.

Once I get this resolved, is there a way to make it default to use the script everytime I open the workbook?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("B3:O210")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub

View 9 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

Closing The Workbook From A Dialog Control

Jun 28, 2006

I have a spreadsheet that automatically opens a dialog box when the workbook is opened. This is a little disclaimer intended for other users of the spreadsheet, which if they click ok it simply closes the dialog sheet, but if they click cancel I want it to kick them out of the Workbook without asking them to save changes. I only want it to close that specific workbook incase they have others open that they haven't saved, etc.

The dialog box is opened as follows:

Private Sub Workbook_Open()
DialogSheets("Splash Screen").Show
End Sub

The code I have tried for the closing of the workbook is done by assigning the following macro to the cancel button (note, all but one of the sheets in my workbook is hidden and protected, including the dialog sheet):

Sub Splash_Close()
'
ThisWorkbook.Close SaveChanges:=False
'
'Also tried ActiveWorkbook.Close SaveChanges:=False
'Application.Quit (This closes all excel documents)
'
End Sub

I know there is something I am missing, keeping in mind I am quite experienced with excel, but relatively new to macros and VB programming.

Just a bit extra on this problem. I am creating the file as a template *.xlt, and the error I get when trying to run the example macro I gave at the start is a 1004 error Method close of object _workbook failed.

Also, in an ineresting (baffling for me at the moment) twist is that once you hit end on the error and it just goes to the first page of your spreadsheet, you can then go to ToolsMacros and run the macro from there, and it closes the workbook exactly the way I want it to!

View 8 Replies View Related

VB Error With Opening/closing Workbook

Jul 6, 2006

I do not understand why my code returns a Run Time Error"9" Subscript out of range. With the following code, I opened a workbook and then wish to close that same workbook without saving. Eventually I will be pulling information from the workbook but for now I just want it to open and then close right away.

Private Sub cb_NewDate_Change()
ListIndex = cb_NewDate.ListIndex

If ListIndex = 0 Then

Application. ScreenUpdating = False

Workbooks.Open Filename:="I:JoeNetwork Conversion FactorsNetworkConversionFactors_Ver_2_0_0.xls"
Application.Workbooks("I:JoeNetwork Conversion FactorsNetworkConversionFactors_Ver_2_0_0.xls").Close False
End Sub

View 3 Replies View Related

Automatically Closing One Workbook When Another Closes

Jul 22, 2006

I have a central workbook that relies on data from other workbooks during the various calculations. I am trying to write some code so that when a user closes the central workbook, all of the other workbooks close as well. I have added my code to the thisWorkbook code, but this does not seem to work. Here is an example of the code. The object is to look for a workbook called Import_Sheet5 and to close it if it is open.

Private Sub Workbook_Close()
Dim Wb As Workbook
Dim sSought As String
sSought = UCase(wbName)
For Each Wb In Application.Workbooks
If wbName = Import_Sheet5 And InStr(Wb. Name, wbName) Then
Wb.Activate
Wb.Close SaveChanges:=False
End If
Next
End Sub

View 7 Replies View Related







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